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.

SECOND ss
MINUTE mm
HOUR hh
DAY DD
MONTH MM
YEAR YY
MINUTE_SECOND mm:ss
HOUR_MINUTE hh:mm
DAY_HOUR DD:hh
YEAR_MONTH YYYY-MM
HOUR_SECOND hh:ss
DAY_MINUTE DD hh:mm
DAY_SECOND DD hh:mm:ss

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

Ian Gilfillan
Ian Gilfillan
Ian Gilfillan lives in Cape Town, South Africa. He is the author of the book 'Mastering MySQL 4', published by Sybex, and has been working with MySQL since 1997. These days he develops mainly in PHP and MySQL, although confesses to starting out with BASIC and COBOL, way back when, and still has a soft spot for Perl. He developed South Africa's first online grocery store, and has developed and taught internet development and other technical courses for various institutions. He has majors in Programming and Information Systems, as well as English and Philosophy. For 5 years he was Lead Developer and IT Manager for Independent Online, South Africa's premier news portal. However, he has now 'retired' from fulltime work, and is hoping that his next book will be more in the style of William Blake and Allen Ginsberg.

Latest Articles