MySQL date and time functions, Part 2

Last time we looked at a few of the more commonly used date and time functions. This time we’re going to look at some of the less well-known functions. First, we will need to understand the various date and time types MySQL accepts for passing to many of these functions.

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                              |
+-----------------------------------------+

You can also use a negative expression to subtract datetimes. To find the datetime 22 hours and 14 minutes before the the 13th July, 2003, 1 minute and 1 second past 1, you can use:

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                                            |
+----------------------------------------------------------------+

Note that when using a datetime type that requires more than a simple numeric, you need to use quotes to contain the entire expression.

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                        |
+--------------------------------------------+

or

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                                   |
+-------------------------------------------------------+

Be careful about where you put the minus sign. When you use quotes, and place the minus sign outside of the quotes, you may not get what you expect. Here’s the same example with the minus sign moved:

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                                   |
+-------------------------------------------------------+

Probably not what you were looking for!

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 |
+-----------------------+

As always, you can use a negative to subtract, as the next example shows (note the two-digit year).

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

Conversely, the PERIOD_DIFF function returns the difference, in months, between two periods. Again, the periods can be specified as both YYYYMM and YYMM.
PERIOD_DIFF(period,period)
For example:

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