Date manipulation with mysql..

Posted on February 10, 2014 by Suresh Kamrushi in PHP

There are various instance when we do date calculation on date / unix datetime fields.Following are some example as how to use inbuilt functions of mysql which will reduce doing calculation in PHP, I hope it will help someone:

SELECT DATE_ADD( '2008-01-02', INTERVAL 1 MONTH )

OR if you want to add date to current day than you can use like this:

Select DATE_ADD(now(), INTERVAL 1 MONTH);

OR if you want to add a month while querying on your table fieldname than just replace now() with your field name like below:

Select DATE_ADD(fieldName, INTERVAL 1 MONTH);

If you are storing date as unix timestamp than than you can do like this:

Select DATE_ADD(FROM_UNIXTIME(1196440219), INTERVAL 1 MONTH);

OR

Select DATE_ADD(FROM_UNIXTIME(fieldName), INTERVAL 1 MONTH);

For doing subtraction you just need to use negative numeric values or DATE_SUB() function can use, like below:

Select DATE_SUB(now(), INTERVAL 1 MONTH);

For unix timestamp:

Select DATE_SUB(FROM_UNIXTIME(fieldName), INTERVAL 1 MONTH);
  Comments or questions are welcome