+-----------------------------------------+
Experiment with a little time manipulation, say adding 72 hours to 9 a.m. Monday morning:
mysql> SELECT DATE_ADD('00-02-07 09:00:00',INTERVAL 36 HOUR);results in
+------------------------------------------------+
| DATE_ADD('00-02-07 09:00:00',INTERVAL 36 HOUR) |
+------------------------------------------------+
| 2000-02-08 21:00:00 |
+------------------------------------------------+
How about finding out what time it was two days ago? Now you'll use DATE_SUB():
mysql> SELECT DATE_SUB(NOW(),INTERVAL 2 DAY);results in
+--------------------------------+
| DATE_SUB(NOW(),INTERVAL 2 DAY) |
+--------------------------------+
| 2000-02-05 18:02:06 |
+--------------------------------+You can also deal with composite intervals; for example, subtract 10 years and 4 months
from[[$I~dates;functions;time, adding]][[$I~functions;dates;time,
adding]][[$I~time;adding]][[$I~adding;time]][[$I~dates;functions;time,
subtracting]][[$I~functions;dates;time, subtracting]][[$I~time;subtracting]][[$I~subtracting;time]] next
December 1:
mysql> SELECT DATE_SUB(20001201,INTERVAL "10 4" YEAR_MONTH);produces
+-----------------------------------------------+
| DATE_SUB(20001201,INTERVAL "10 4" YEAR_MONTH) |
+-----------------------------------------------+
| 1990-08-01 |
+-----------------------------------------------+
Note If you fail to specify the full field length required of an interval, MySQL assumes
you have left out the leftmost part of the field. So
DATE_SUB(20001201,INTERVAL "10" YEAR_MONTH) would return 2000-02-
01, subtracting 10 months but no years.
PERIOD_ADD(period,months) will take a period in the format YYMM or YYYYMM and add a
certain number of months to it. (A period in the format YYMM will be assumed to contain an
abbreviated 4-digit year and converted accordingly.) For example, you can add 12 months to June '08:
mysql> SELECT PERIOD_ADD(0806,12);
results in
+---------------------+
| PERIOD_ADD(0806,12) |
+---------------------+
| 200906 |
+---------------------+
You can use the same function to subtract; take 2 months away from April 2000:
mysql> SELECT PERIOD_ADD(200004,-2);