Database Reference
In-Depth Information
| 2014-02-20 | 2014-02-13 |
+------------+-------------------------------------+
• For questions where you need to know both the date and the time, begin with a
DATETIME
or
TIMESTAMP
value. To answer the question, “What time will it be in 60
hours?” do this:
mysql>
SELECT NOW(), DATE_ADD(NOW(),INTERVAL 60 HOUR);
+---------------------+----------------------------------+
| NOW() | DATE_ADD(NOW(),INTERVAL 60 HOUR) |
+---------------------+----------------------------------+
| 2014-02-20 18:07:06 | 2014-02-23 06:07:06 |
+---------------------+----------------------------------+
• Some interval specifiers have both date and time parts. The following adds 14.5
hours to the current date and time:
mysql>
SELECT NOW(), DATE_ADD(NOW(),INTERVAL '14:30' HOUR_MINUTE);
+---------------------+----------------------------------------------+
| NOW() | DATE_ADD(NOW(),INTERVAL '14:30' HOUR_MINUTE) |
+---------------------+----------------------------------------------+
| 2014-02-20 18:07:06 | 2014-02-21 08:37:06 |
+---------------------+----------------------------------------------+
Similarly, adding 3 days and 4 hours produces this result:
mysql>
SELECT NOW(), DATE_ADD(NOW(),INTERVAL '3 4' DAY_HOUR);
+---------------------+-----------------------------------------+
| NOW() | DATE_ADD(NOW(),INTERVAL '3 4' DAY_HOUR) |
+---------------------+-----------------------------------------+
| 2014-02-20 18:07:06 | 2014-02-23 22:07:06 |
+---------------------+-----------------------------------------+
DATE_ADD()
and
DATE_SUB()
are interchangeable because one is the same as the other
with the sign of the interval value flipped. These two expressions are equivalent for any
date value
d
:
DATE_ADD(d,INTERVAL -3 MONTH)
DATE_SUB(d,INTERVAL 3 MONTH)
You can also use the
+
INTERVAL
or
-
INTERVAL
operator to perform date interval addition
or subtraction:
mysql>
SELECT CURDATE(), CURDATE() + INTERVAL 1 YEAR;
+------------+-----------------------------+
| CURDATE() | CURDATE() + INTERVAL 1 YEAR |
+------------+-----------------------------+
| 2014-02-20 | 2015-02-20 |
+------------+-----------------------------+
mysql>
SELECT NOW(), NOW() - INTERVAL '1 12' DAY_HOUR;
+---------------------+----------------------------------+
| NOW() | NOW() - INTERVAL '1 12' DAY_HOUR |
+---------------------+----------------------------------+
