Jump to content

Archived

This topic is now archived and is closed to further replies.

matfish

datetime search

Recommended Posts

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

Share this post


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

Share this post


Link to post
Share on other sites
it's as easy as MONTH():
[code]
SELECT * FROM tableName WHERE MONTH(dateCol) = '2';
[/code]

that would return all records from february

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
Inserting using php, would I also need ' ' around the 'NOW()' or just NOW()?

Many thanks

Share this post


Link to post
Share on other sites
Just now() without the quotes...

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

Regards
Rich

Share this post


Link to post
Share on other sites
Hi,

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

expirydate = now() + 1 year.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
Hi- thanks for the replies.

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


Thanks

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
Hi - thank you - Works a treat!

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

Much appreciated!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
[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

Share this post


Link to post
Share on other sites
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...

Share this post


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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
Its ok, $packageexpiry = date("d-m-Y", strtotime("-5 days", strtotime($hosting['packageexpirydate']))); kinda did it

Share this post


Link to post
Share on other sites
brilliant- this helped me out too! was trying to format just a TIME and DATE separately- THANKS!  ;D

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.