Jump to content


Photo

datetime search


  • Please log in to reply
27 replies to this topic

#1 matfish

matfish
  • Members
  • PipPipPip
  • Advanced Member
  • 242 posts
  • LocationUK

Posted 16 August 2006 - 01:27 PM

Hi,

Having a few probs php/mysql.

Got a table with datetime (0000-00-00 00:00:00) and needing to do a mysql query on the months only (like archiving the months only) but how do I search for the months out of that type of date?

Many thanks

#2 Daniel0

Daniel0
  • Staff Alumni
  • Advanced Member
  • 11,956 posts

Posted 16 August 2006 - 01:42 PM

I'm not sure if this will work, but try something like:
SELECT * FROM whatever WHERE date LIKE '%-month_here-% %:%:%';


#3 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 16 August 2006 - 02:05 PM

it's as easy as MONTH():
SELECT * FROM tableName WHERE MONTH(dateCol) = '2';

that would return all records from february
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#4 matfish

matfish
  • Members
  • PipPipPip
  • Advanced Member
  • 242 posts
  • LocationUK

Posted 16 August 2006 - 02:36 PM

Brilliant - thank you

#5 matfish

matfish
  • Members
  • PipPipPip
  • Advanced Member
  • 242 posts
  • LocationUK

Posted 05 September 2006 - 02:32 PM

Got a table with datetime (0000-00-00 00:00:00) and need to insert the date one year on from today.

How do I go about doing this? Also do you need ' ' around the datetime when inserting into the database? HAving a bit of trouble...

Many thanks

#6 matfish

matfish
  • Members
  • PipPipPip
  • Advanced Member
  • 242 posts
  • LocationUK

Posted 05 September 2006 - 02:38 PM

Inserting using php, would I also need ' ' around the 'NOW()' or just NOW()?

Many thanks

#7 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 05 September 2006 - 02:47 PM

Just now() without the quotes...

$sql="INSERT into table_name (col1, col2, col3) VALUES ("data1", "data2", now())";

Regards
Rich
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#8 matfish

matfish
  • Members
  • PipPipPip
  • Advanced Member
  • 242 posts
  • LocationUK

Posted 05 September 2006 - 02:50 PM

Thats cool thanks - any ideas on adding on months/years etc?

Thanks

#9 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 05 September 2006 - 02:53 PM

Is the field type 'datetime'?

Rich
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#10 matfish

matfish
  • Members
  • PipPipPip
  • Advanced Member
  • 242 posts
  • LocationUK

Posted 05 September 2006 - 02:57 PM

Hi,

yeah the fieldtype is datetime. Trying to make an expiry date from an order, so:

expirydate = now() + 1 year.

#11 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 05 September 2006 - 03:03 PM

OK, this can be one of two ways, you can manipulate the date in PHP first, or when you insert it into the database.

I'm assuming you're using MySQL?

Rich
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#12 matfish

matfish
  • Members
  • PipPipPip
  • Advanced Member
  • 242 posts
  • LocationUK

Posted 05 September 2006 - 03:05 PM

Hi- thanks for the replies.

Yes using MySQL, been trying to manipulate it in php before inserting with no joy  :(


Thanks

#13 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 05 September 2006 - 03:13 PM

if you're wanting to manipulate with PHP, just use the following for one year from today:
<?php
// insert this value into the datetime field
$date = date('Y-m-d', strtotime("today + 1 year"));
?>

if you'd rather do it with mySQL directly, you can do the following query:
INSERT INTO tableName (dateCol) VALUES (DATE_ADD(CURDATE(), INTERVAL 1 YEAR));

hope this helps
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#14 matfish

matfish
  • Members
  • PipPipPip
  • Advanced Member
  • 242 posts
  • LocationUK

Posted 05 September 2006 - 03:20 PM

Hi - thank you - Works a treat!

I gather if I wanted to add on months the same would apply?

Much appreciated!

#15 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 05 September 2006 - 03:22 PM

Beat me to it  :(

I had the comma missing from my statement when testing it and couldn't figure out why this wasn't working...

INSERT into tableName (start_date, end_date) VALUES (now(), DATE_ADD(now() INTERVAL 1 YEAR));

Well done Obs.
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#16 matfish

matfish
  • Members
  • PipPipPip
  • Advanced Member
  • 242 posts
  • LocationUK

Posted 05 September 2006 - 03:24 PM

I thank you both for your prompt replies!

#17 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 05 September 2006 - 03:24 PM

Hi - thank you - Works a treat!

I gather if I wanted to add on months the same would apply?

Much appreciated!


yes, check out the INTERVAL keyword in the mysql manual. you can use it to declare anything from seconds to years to whatever.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#18 matfish

matfish
  • Members
  • PipPipPip
  • Advanced Member
  • 242 posts
  • LocationUK

Posted 05 September 2006 - 03:31 PM

how about if I was using the php manipulation way?

#19 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 05 September 2006 - 04:14 PM

how about if I was using the php manipulation way?


same idea. read up in the manual on strtotime(). it will handle practically any human readable date format or interval and calculate the differences.

good luck
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#20 matfish

matfish
  • Members
  • PipPipPip
  • Advanced Member
  • 242 posts
  • LocationUK

Posted 08 September 2006 - 03:34 PM

Hi-again.

How do I read a datetime (0000-00-00 00:00:00) easily in php? I just wanted day-month-year? It keeps bringing back a stupid date of 1st Jan 1970...

Thanks...




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users