datesub question

Question

Hi,

is it possible to use date_sub like this ?

$dt = date("Y-m-d h:i:s");

$query = "INSERT INTO `table` (`date`) VALUES ('DATE_SUB('$dt', INTERVAL 15 DAY)')";

$result = MYSQL_QUERY($query);

Thanks

Answer 1

I'd advise you not to leave calendar calculations to the database. Use the DateTime class instead and have the timezonedb extension updated. This is actually one those things PHP gets right.

See also this post on Derick's blog.

Answer 2

You could use it on database by removing the quotes around DATE_SUB(...). But you should consider using the unix time stamp with time() and reconverting it to a date format using the date() function, like this:

$dt = date("Y-m-d h:i:s", time() - 15*24*3600); //today - 15 days

Works with PHP 4 and PHP 5.

Answer 3

This is wrong 'DATE_SUB('$dt', INTERVAL 15 DAY)', that's a string.

Now, your date format is same as the format used by MySQL's NOW() function.

SELECT NOW();
---------------------
|now()              |
---------------------
|2010-05-15 20:42:35|
---------------------

And since I've been using without any problem DATE_SUB(NOW(), INTERVAL 6 MONTH); in a recent project, I don't see any reason why you could not use it.

SELECT DATE_SUB( NOW( ) , INTERVAL 6 MONTH );
-----------------------------------
|DATE_SUB(NOW(), INTERVAL 6 MONTH)|
-----------------------------------
|2009-11-15 20:49:28              |
-----------------------------------