MySQL Cookbook

5.17 Adding a Temporal Interval to a Date

5.17.1 Problem

You want to add time to a date or date-and-time value.

5.17.2 Solution

Use DATE_ADD( ) and DATE_SUB( ), functions intended specifically for date arithmetic. You can also use TO_DAYS( ) and FROM_DAYS( ), or UNIX_TIMESTAMP( ) and FROM_UNIXTIME( ).

5.17.3 Discussion

Date arithmetic is less straightforward than time arithmetic due to the varying length of months and years, so MySQL provides special functions DATE_ADD( ) and DATE_SUB( ) for adding or subtracting intervals to or from dates.^[4] Each function takes a date value d and an interval, expressed using the following syntax:

^[4] DATE_ADD( ) and DATE_SUB( ) were introduced in MySQL 3.22.4, as were their synonyms, ADDDATE( ) and SUBDATE( ).

DATE_ADD(d,INTERVAL val unit)
DATE_SUB(d,INTERVAL val unit)

Here, unit is the interval unit and val is an expression indicating the number of units. Some of the common unit specifiers are YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. (Check the MySQL Reference Manual for the full list.) Note that all these units are specified in singular form, not plural.

Using DATE_ADD( ) or DATE_SUB( ), you can perform date arithmetic operations such as the following:

• Determine the date three days from today:

  mysql> SELECT CURDATE( ), DATE_ADD(CURDATE( ),INTERVAL 3 DAY);
  +------------+------------------------------------+
  | CURDATE( )  | DATE_ADD(CURDATE( ),INTERVAL 3 DAY) |
  +------------+------------------------------------+
  | 2002-07-15 | 2002-07-18                         |
  +------------+------------------------------------+

• Find the date a week ago (the query here uses 7 DAY to represent an interval of a week because there is no WEEK interval unit):

  mysql> SELECT CURDATE( ), DATE_SUB(CURDATE( ),INTERVAL 7 DAY);
  +------------+------------------------------------+
  | CURDATE( )  | DATE_SUB(CURDATE( ),INTERVAL 7 DAY) |
  +------------+------------------------------------+
  | 2002-07-15 | 2002-07-08                         |
  +------------+------------------------------------+

• For questions where you need to know both the date and the time, begin with a DATETIME or TIMESTAMP value. To answer the question, "what time will it be in 60 hours?," do this:

  mysql> SELECT NOW( ), DATE_ADD(NOW( ),INTERVAL 60 HOUR);
  +---------------------+----------------------------------+
  | NOW( )               | DATE_ADD(NOW( ),INTERVAL 60 HOUR) |
  +---------------------+----------------------------------+
  | 2002-07-15 11:31:17 | 2002-07-17 23:31:17              |
  +---------------------+----------------------------------+

• Some interval specifiers comprise both date and time parts. The following adds 14 and a half hours to the current date and time:

  mysql> SELECT NOW( ), DATE_ADD(NOW( ),INTERVAL '14:30' HOUR_MINUTE);
  +---------------------+----------------------------------------------+
  | NOW( )               | DATE_ADD(NOW( ),INTERVAL '14:30' HOUR_MINUTE) |
  +---------------------+----------------------------------------------+
  | 2002-07-15 11:31:24 | 2002-07-16 02:01:24                          |
  +---------------------+----------------------------------------------+

  Similarly, adding 3 days and 4 hours produces this result:

  mysql> SELECT NOW( ), DATE_ADD(NOW( ),INTERVAL '3 4' DAY_HOUR);
  +---------------------+-----------------------------------------+
  | NOW( )               | DATE_ADD(NOW( ),INTERVAL '3 4' DAY_HOUR) |
  +---------------------+-----------------------------------------+
  | 2002-07-15 11:31:30 | 2002-07-18 15:31:30                     |
  +---------------------+-----------------------------------------+

DATE_ADD( ) and DATE_SUB( ) are interchangeable because one is the same as the other with the sign of the interval value flipped. For example, these two calls are equivalent for any date value d:

DATE_ADD(d,INTERVAL -3 MONTH)
DATE_SUB(d,INTERVAL 3 MONTH)

As of MySQL 3.23.4, you can also use the + and - operators to perform date interval addition and subtraction:

mysql> SELECT CURDATE( ), CURDATE( ) + INTERVAL 1 YEAR;
+------------+-----------------------------+
| CURDATE( )  | CURDATE( ) + INTERVAL 1 YEAR |
+------------+-----------------------------+
| 2002-07-15 | 2003-07-15                  |
+------------+-----------------------------+
mysql> SELECT NOW( ), NOW( ) - INTERVAL 24 HOUR;
+---------------------+--------------------------+
| NOW( )               | NOW( ) - INTERVAL 24 HOUR |
+---------------------+--------------------------+
| 2002-07-15 11:31:48 | 2002-07-14 11:31:48      |
+---------------------+--------------------------+

Another way to add intervals to date or date-and-time values is by using functions that convert to and from basic units. For example, to shift a date forward or backward a week (seven days), use TO_DAYS( ) and FROM_DAYS( ):

mysql> SET @d = '2002-01-01';
mysql> SELECT @d AS date,
    -> FROM_DAYS(TO_DAYS(@d) + 7) AS 'date + 1 week',
    -> FROM_DAYS(TO_DAYS(@d) - 7) AS 'date - 1 week';
+------------+---------------+---------------+
| date       | date + 1 week | date - 1 week |
+------------+---------------+---------------+
| 2002-01-01 | 2002-01-08    | 2001-12-25    |
+------------+---------------+---------------+

TO_DAYS( ) also can convert DATETIME or TIMESTAMP values to days, if you don't mind having it chop off the time part:

mysql> SET @dt = '2002-01-01 12:30:45';
mysql> SELECT @dt AS datetime,
    -> FROM_DAYS(TO_DAYS(@dt) + 7) AS 'datetime + 1 week',
    -> FROM_DAYS(TO_DAYS(@dt) - 7) AS 'datetime - 1 week';
+---------------------+-------------------+-------------------+
| datetime            | datetime + 1 week | datetime - 1 week |
+---------------------+-------------------+-------------------+
| 2002-01-01 12:30:45 | 2002-01-08        | 2001-12-25        |
+---------------------+-------------------+-------------------+

To preserve accuracy with DATETIME or TIMESTAMP values, use UNIX_TIMESTAMP( ) and FROM_UNIXTIME( ) instead. The following query shifts a DATETIME value forward and backward by an hour (3600 seconds):

mysql> SET @dt = '2002-01-01 09:00:00';
mysql> SELECT @dt AS datetime,
    -> FROM_UNIXTIME(UNIX_TIMESTAMP(@dt) + 3600) AS 'datetime + 1 hour',
    -> FROM_UNIXTIME(UNIX_TIMESTAMP(@dt) - 3600) AS 'datetime - 1 hour';
+---------------------+---------------------+---------------------+
| datetime            | datetime + 1 hour   | datetime - 1 hour   |
+---------------------+---------------------+---------------------+
| 2002-01-01 09:00:00 | 2002-01-01 10:00:00 | 2002-01-01 08:00:00 |
+---------------------+---------------------+---------------------+

The last technique requires that both your initial value and the resulting value like in the allowable range for TIMESTAMP values (1970 to sometime in the year 2037).