MySQL date and time functions, Part 2

As you can see, there are quite a few. Notice that the ones spanning a number of time elements (such as DAY_SECOND) include all the elements between DAY and SECOND (in this case HOUR and MINUTE). In the last article, we looked at a specific date calculation to determine age. However, there's a lot more you can do in MySQL before you need to call upon a programming language for assistance.

Adding and subtracting dates and times with DATE_ADD() and DATE_SUB()

The DATE_ADD() function - ADDDATE() is a synonmym - is used to add a particular date or time interval to a give date or time.
DATE_ADD(datetime, INTERVAL expression datetimetype)
For example, to find a date 14 days after the 13th July, 2003, you can use:

mysql> SELECT DATE_ADD('2003-07-13', INTERVAL 14 DAY);
+-----------------------------------------+
| DATE_ADD('2003-07-13', INTERVAL 14 DAY) |
+-----------------------------------------+
| 2003-07-27                              |
+-----------------------------------------+

mysql> SELECT DATE_ADD('2003-07-13 01:01:01', INTERVAL -'22:14' HOUR_MINUTE);
+----------------------------------------------------------------+
| DATE_ADD('2003-07-13 01:01:01', INTERVAL -'22:14' HOUR_MINUTE) |
+----------------------------------------------------------------+
| 2003-07-13 00:39:01                                            |
+----------------------------------------------------------------+

You can also mix date and time types, and MySQL will do its best to make do. For example:

mysql> SELECT DATE_ADD('2003-07-13', INTERVAL -1 MINUTE);
+--------------------------------------------+
| DATE_ADD('2003-07-13', INTERVAL -1 MINUTE) |
+--------------------------------------------+
| 2003-07-12 23:59:00                        |
+--------------------------------------------+

mysql> SELECT DATE_ADD('2003-07-13', INTERVAL '-22:14' HOUR_MINUTE);
+-------------------------------------------------------+
| DATE_ADD('2003-07-13', INTERVAL '-22:14' HOUR_MINUTE) |
+-------------------------------------------------------+
| 2003-07-12 01:46:00                                   |
+-------------------------------------------------------+

mysql> SELECT DATE_ADD('2003-07-14', INTERVAL -'22:14' HOUR_MINUTE);
+-------------------------------------------------------+
| DATE_ADD('2003-07-14', INTERVAL -'22:14' HOUR_MINUTE) |
+-------------------------------------------------------+
| 2003-07-13 23:38:00                                   |
+-------------------------------------------------------+

There's an alternative to using a negative number with the DATE_ADD() function - you could simply use DATE_SUB(), or its synonym SUBDATE(). There is also an alternative if you're only worried about the YEAR and MONTH components of the date. You can use the PERIOD_ADD() and PERIOD_DIFF() functions. PERIOD_ADD takes a period (specified as YYYYMM or YYMM), and adds a number of months
PERIOD_ADD(period,months)
For example:

mysql> SELECT PERIOD_ADD(200312,43);
+-----------------------+
| PERIOD_ADD(200312,43) |
+-----------------------+
|                200707 |
+-----------------------+

mysql> SELECT PERIOD_ADD(0312,-32);
+----------------------+
| PERIOD_ADD(0312,-32) |
+----------------------+
|               200104 |
+----------------------+

mysql> SELECT PERIOD_DIFF(200104,0312);
+--------------------------+
| PERIOD_DIFF(200104,0312) |
+--------------------------+
|                      -32 |
+--------------------------+