The Number Game - MySQL

In addition to futzing with days and dates, MySQL also provides you with the ability to perform date arithmetic on specific date and time values, with its DATE_ADD() and DATE_SUB() functions. The syntax of these functions is somewhat more complex than the ones you've seen thus far - here's what it looks like:

DATE_ADD(startDate, INTERVAL period periodType)

DATE_SUB(startDate, INTERVAL period periodType)

In order to better understand this, consider the following example, which adds 1 year to the specified start date and returns the new value:

mysql> SELECT DATE_ADD('2003-04-15', INTERVAL 1 YEAR);

+-----------------------------------------+

| DATE_ADD('2003-04-15', INTERVAL 1 YEAR) |

+-----------------------------------------+

| 2004-04-15 |

+-----------------------------------------+

1 row in set (0.18 sec)

Here's another one, this one adding 3 hours and 45 minutes to the starting timestamp,

mysql> SELECT DATE_ADD('2003-04-15 02:02', INTERVAL "03:45" 

mysql> HOUR_MINUTE);

+------------------------------------------------------------+

| DATE_ADD('2003-04-15 02:02', INTERVAL "03:45" HOUR_MINUTE) |

+------------------------------------------------------------+

| 2003-04-15 05:47:00 |

+------------------------------------------------------------+

1 row in set (0.01 sec)

and yet another, this one adding 7 days, 1 hour, 55 minutes and 10 seconds to December 25 2000:

mysql> SELECT DATE_ADD('2000-12-25 12:00:00', INTERVAL "7 01:55:10"

DAY_SECOND);

+-------------------------------------------------------------------+

| DATE_ADD('2000-12-25 12:00:00', INTERVAL "7 01:55:10" DAY_SECOND) 
|

+-------------------------------------------------------------------+

| 2001-01-01 13:55:10 |

+-------------------------------------------------------------------+

1 row in set (0.01 sec)

In order to better understand this, it's necessary to delve a little deeper into the syntax of the DATE_ADD() and DATE_SUB() functions:

1. The first parameter, startDate, is a date or time value in either string ("YYYY-MM-DD HH:MM:SS") or number (YYYYMMDDHHMMSS) format. This parameter specifies the date to use as a base for the calculation.

2. The second parameter consists of three separate components, which together specify the interval which is to be added to (or subtracted from) the first parameter. The components consist of the keyword INTERVAL, followed by the interval period and a keyword providing information on the interval calculation to be performed.

The relationship between the formatting of the interval period and the keyword following it is fixed, and illustrated in the following table:

period (formatted as) periodType
---------------------------------------------
SECONDS SECOND
MINUTES MINUTE
HOURS HOUR
DAYS DAY
MONTHS MONTH
YEARS YEAR
"MINUTES:SECONDS" MINUTE_SECOND
"HOURS:MINUTES" HOUR_MINUTE
"DAYS HOURS" DAY_HOUR
"YEARS-MONTHS" YEAR_MONTH
"HOURS:MINUTES:SECONDS" HOUR_SECOND
"DAYS HOURS:MINUTES" DAY_MINUTE
"DAYS HOURS:MINUTES:SECONDS" DAY_SECOND

With this in mind, the examples above should become much clearer. If, for example, you wanted to perform a calculation involving interval units of 1 year, you could format your arguments using the YEAR type, whereas if you wanted to perform date arithmetic with hours and minutes, you could format your arguments using the HOUR_MINUTE or HOUR_SECOND types.

Let's look at a few more examples of this in action.

mysql> SELECT DATE_ADD(20041130, INTERVAL 1 MONTH);

+--------------------------------------+

| DATE_ADD(20041130, INTERVAL 1 MONTH) |

+--------------------------------------+

| 2004-12-30 |

+--------------------------------------+

1 row in set (0.88 sec)

mysql> SELECT DATE_ADD(20041130, INTERVAL 31 DAY);

+-------------------------------------+

| DATE_ADD(20041130, INTERVAL 31 DAY) |

+-------------------------------------+

| 2004-12-31 |

+-------------------------------------+

1 row in set (0.02 sec)
mysql> SELECT DATE_ADD('2003-09-17 03:30:00', INTERVAL "02:30"

HOUR_MINUTE); 

+---------------------------------------------------------------+

| DATE_ADD('2003-09-17 03:30:00', INTERVAL "02:30" HOUR_MINUTE) |

+---------------------------------------------------------------+

| 2003-09-17 06:00:00 |

+---------------------------------------------------------------+

1 row in set (0.01 sec)
mysql> SELECT DATE_SUB('2003-01-01', INTERVAL 24 HOUR);

+------------------------------------------+

| DATE_SUB('2003-01-01', INTERVAL 24 HOUR) |

+------------------------------------------+

| 2002-12-31 00:00:00 |

+------------------------------------------+

1 row in set (0.00 sec)
mysql> SELECT DATE_SUB('2003-09-17 12:00:00', INTERVAL "1 12" 
DAY_HOUR);



+-----------------------------------------------------------+

| DATE_SUB('2003-09-17 12:00:00', INTERVAL "1 12" DAY_HOUR) |

+-----------------------------------------------------------+

| 2003-09-16 00:00:00 |

+-----------------------------------------------------------+

1 row in set (0.00 sec)
mysql> SELECT DATE_SUB('2003-01-01 18:45:10', INTERVAL "17:45:10"

HOUR_SECOND);

+------------------------------------------------------------------+

| DATE_SUB('2003-01-01 18:45:10', INTERVAL "17:45:10" HOUR_SECOND) 
|

+------------------------------------------------------------------+

| 2003-01-01 01:00:00 |

+------------------------------------------------------------------+

1 row in set (0.00 sec)