datetime search

[matfish]

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

[Daniel0]

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

[obsidian]

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

that would return all records from february

[matfish]

Brilliant - thank you

[matfish]

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

[matfish]

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

Many thanks

[HuggieBear]

Just now() without the quotes...

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

Regards
Rich

[matfish]

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

Thanks

[HuggieBear]

Is the field type 'datetime'?

Rich

[matfish]

Hi,

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

expirydate = now() + 1 year.

[HuggieBear]

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

[matfish]

Hi- thanks for the replies.

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


Thanks

[obsidian]

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

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

hope this helps

[matfish]

Hi - thank you - Works a treat!

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

Much appreciated!

[HuggieBear]

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...

[code]
INSERT into tableName (start_date, end_date) VALUES (now(), DATE_ADD(now() INTERVAL 1 YEAR));
[/code]

Well done Obs.

[matfish]

I thank you both for your prompt replies!

[obsidian]

[quote author=matfish link=topic=104492.msg428501#msg428501 date=1157469633]
Hi - thank you - Works a treat!

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

Much appreciated!
[/quote]

yes, check out the INTERVAL keyword in the mysql manual. you can use it to declare anything from seconds to years to whatever.

[matfish]

how about if I was using the php manipulation way?

[obsidian]

[quote author=matfish link=topic=104492.msg428516#msg428516 date=1157470287]
how about if I was using the php manipulation way?
[/quote]

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

[matfish]

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...

[obsidian]

check out strtotime():
[code]
<?php
$ts = "2006-02-14 12:00:00";
echo date('y-m-d', strtotime($ts));
?>
[/code]

[matfish]

Perfect once again, thank you!

[matfish]

Hi,

Dont know why I have so much trouble with dates... but - in regards to the above (using datetime) how can I -5days (or whatever) from a date from the database? It keeps bringing back weird dates etc..

Many thanks

[matfish]

Its ok, $packageexpiry = date("d-m-Y", strtotime("-5 days", strtotime($hosting['packageexpirydate']))); kinda did it

[coalduststar]

brilliant- this helped me out too! was trying to format just a TIME and DATE separately- THANKS!  ;D