Date Arithmetic With MySQL - The Number Game (
Page 4 of 8 )
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)
/ 35 
