Date Arithmetic With MySQL - Artificial Intelligence (
Page 5 of 8 )
It should be noted that MySQL includes intelligence to automatically format
the result of the calculation as either a date-only or date-and-time value. For
example, if your arguments to DATE_ADD() or DATE_SUB() contains only year, month
and day components, MySQL will output a date-only value as result.
mysql> SELECT DATE_SUB(20041130, INTERVAL 3 MONTH);
+--------------------------------------+
| DATE_SUB(20041130, INTERVAL 3 MONTH) |
+--------------------------------------+
| 2004-08-30 |
+--------------------------------------+
1 row in set (0.00 sec)
However, if your arguments include a time component - hours, minutes or
seconds - then MySQL produces a result containing both date and time
components.
mysql> SELECT DATE_ADD('2010-02-14', INTERVAL "1 1" DAY_HOUR);
+-------------------------------------------------+
| DATE_ADD('2010-02-14', INTERVAL "1 1" DAY_HOUR) |
+-------------------------------------------------+
| 2010-02-15 01:00:00 |
+-------------------------------------------------+
1 row in set (0.01 sec)
If you use illegal date or time values, MySQL will still attempt to return a
valid result by performing adjustments on the various values.
mysql> SELECT DATE_ADD('2010-02-30', INTERVAL 1 DAY);
+----------------------------------------+
| DATE_ADD('2010-02-30', INTERVAL 1 DAY) |
+----------------------------------------+
| 2010-03-03 |
+----------------------------------------+
1 row in set (0.00 sec)
MySQL also provides an alternative syntax to DATE_ADD() and DATE_SUB(), which
is sometimes more readable - this involves using + and - signs to indicate the
type of calculation to be performed. Consider the
following examples, which
illustrate:
mysql> SELECT 20000615 + INTERVAL 5 DAY;
+---------------------------+
| 20000615 + INTERVAL 5 DAY |
+---------------------------+
| 2000-06-20 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT 20000615000000 - INTERVAL 10 SECOND ;
+-------------------------------------+
| 20000615000000 - INTERVAL 10 SECOND |
+-------------------------------------+
| 2000-06-14 23:59:50 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT '2000-06-15 13:00' - INTERVAL "2-1" YEAR_MONTH;
+------------------------------------------------+
| '2000-06-15 13:00' - INTERVAL "2-1" YEAR_MONTH |
+------------------------------------------------+
| 1998-05-15 13:00:00 |
+------------------------------------------------+
1 row in set (0.00 sec)
/ 35 
