Recipe 6.10. Adding Date or Time Values

Problem

You want to add temporal values. For example, you want to add a given number of seconds to a time or determine what the date will be three weeks from today.

Solution

To add date or time values, you have several options:

• Use one of the temporal-addition functions.

• Use the + INTERVAL or - INTERVAL operator.

• Convert the values to basic units, and take the sum.

The allowable functions or operators depend on the types of the values that you want to add.

Discussion

The following discussion shows several ways to add temporal values.

Adding temporal values using temporal-addition functions or operators

To add a time or date-and-time value and a time value, use the ADDTIME⁠(⁠ ⁠ ⁠) function:

mysql> SET @t1 = '12:00:00', @t2 = '15:30:00'; mysql> SELECT ADDTIME(@t1,@t2); +------------------+ | ADDTIME(@t1,@t2) | +------------------+ | 27:30:00         | +------------------+ mysql> SET @dt = '1984-03-01 12:00:00', @t = '12:00:00'; mysql> SELECT ADDTIME(@dt,@t); +---------------------+ | ADDTIME(@dt,@t)     | +---------------------+ | 1984-03-02 00:00:00 | +---------------------+ 

To add a date or date-and-time value and a time value, use the TIMESTAMP⁠(⁠ ⁠ ⁠) function:

mysql> SET @d = '1984-03-01', @t = '15:30:00'; mysql> SELECT TIMESTAMP(@d,@t); +---------------------+ | TIMESTAMP(@d,@t)    | +---------------------+ | 1984-03-01 15:30:00 | +---------------------+ mysql> SET @dt = '1984-03-01 12:00:00', @t = '12:00:00'; mysql> SELECT TIMESTAMP(@dt,@t); +---------------------+ | TIMESTAMP(@dt,@t)   | +---------------------+ | 1984-03-02 00:00:00 | +---------------------+ 

MySQL also provides the DATE_ADD⁠(⁠ ⁠ ⁠) and DATE_SUB⁠(⁠ ⁠ ⁠) functions for adding intervals to dates and subtracting intervals from dates. Each function takes a date (or date-and-time) value d and an interval, expressed using the following syntax:

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

The + INTERVAL and - INTERVAL operators are similar:

d + INTERVAL val unit d - INTERVAL val unit                

unit is the interval unit and val is an expression indicating the number of units. Some of the common unit specifiers are SECOND, MINUTE, HOUR, DAY, MONTH, and YEAR. (Check the MySQL Reference Manual for the full list.) Note that each of these unit specifiers is given 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) | +------------+------------------------------------+ | 2006-05-22 | 2006-05-25                         | +------------+------------------------------------+ 

  
  

• Find the date a week ago:

  mysql> SELECT CURDATE(), DATE_SUB(CURDATE(),INTERVAL 7 DAY); +------------+------------------------------------+ | CURDATE()  | DATE_SUB(CURDATE(),INTERVAL 7 DAY) | +------------+------------------------------------+ | 2006-05-22 | 2006-05-15                         | +------------+------------------------------------+ 

  
  

  As of MySQL 5.0, you can use 1 WEEK instead of 7 DAY, but the result is a DATETIME value rather than a DATE value.

• 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) | +---------------------+----------------------------------+ | 2006-02-04 09:28:10 | 2006-02-06 21:28:10              | +---------------------+----------------------------------+ 

  
  

• Some interval specifiers have both date and time parts. The following adds 14.5 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) | +---------------------+----------------------------------------------+ | 2006-02-04 09:28:31 | 2006-02-04 23:58:31                          | +---------------------+----------------------------------------------+ 

  
  

  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) | +---------------------+-----------------------------------------+ | 2006-02-04 09:28:38 | 2006-02-07 13:28:38                     | +---------------------+-----------------------------------------+ 

  
  

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

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

You can also use the + INTERVAL and - INTERVAL operators to perform date interval addition and subtraction:

mysql> SELECT CURDATE(), CURDATE() + INTERVAL 1 YEAR; +------------+-----------------------------+ | CURDATE()  | CURDATE() + INTERVAL 1 YEAR | +------------+-----------------------------+ | 2006-05-22 | 2007-05-22                  | +------------+-----------------------------+ mysql> SELECT NOW(), NOW() - INTERVAL '1 12' DAY_HOUR; +---------------------+----------------------------------+ | NOW()               | NOW() - INTERVAL '1 12' DAY_HOUR | +---------------------+----------------------------------+ | 2006-05-22 19:00:50 | 2006-05-21 07:00:50              | +---------------------+----------------------------------+ 

An alternative function for adding intervals to date or date-and-time values is TIMESTAMPADD⁠(⁠ ⁠ ⁠), available in MySQL 5.0 or higher. Its arguments are similar to those for DATE_ADD⁠(⁠ ⁠ ⁠), and, in fact, the following equivalence holds:

TIMESTAMPADD(unit,interval,d) = DATE_ADD(d,INTERVAL interval                    unit) 

Adding temporal values using basic units

Another way to add intervals to date or date-and-time values is to perform temporal "shifting" via functions that convert to and from basic units. For background information about the applicable functions, see Section 6.8.

Adding time values using basic units. Adding times with basic units is similar to calculating intervals between times, except that you compute a sum rather than a difference. To add an interval value in seconds to a TIME value, convert the TIME to seconds so that both values are represented in the same units, add the values together, and convert the result back to a TIME. For example, two hours is 7200 seconds (2 x 60 x 60), so the following statement adds two hours to each t1 value in the time_val table:

mysql> SELECT t1,     -> SEC_TO_TIME(TIME_TO_SEC(t1) + 7200) AS 't1 plus 2 hours'     -> FROM time_val; +----------+-----------------+ | t1       | t1 plus 2 hours | +----------+-----------------+ | 15:00:00 | 17:00:00        | | 05:01:30 | 07:01:30        | | 12:30:20 | 14:30:20        | +----------+-----------------+ 

If the interval itself is expressed as a TIME, it too should be converted to seconds before adding the values together. The following example calculates the sum of the two TIME values in each row of the time_val table:

mysql> SELECT t1, t2,     -> TIME_TO_SEC(t1) + TIME_TO_SEC(t2)     ->   AS 't1 + t2 (in seconds)',     -> SEC_TO_TIME(TIME_TO_SEC(t1) + TIME_TO_SEC(t2))     ->   AS 't1 + t2 (as TIME)'     -> FROM time_val; +----------+----------+----------------------+-------------------+ | t1       | t2       | t1 + t2 (in seconds) | t1 + t2 (as TIME) | +----------+----------+----------------------+-------------------+ | 15:00:00 | 15:00:00 |               108000 | 30:00:00          | | 05:01:30 | 02:30:20 |                27110 | 07:31:50          | | 12:30:20 | 17:30:45 |               108065 | 30:01:05          | +----------+----------+----------------------+-------------------+ 

It's important to recognize that MySQL TIME values really represent elapsed time, not time of day, so they don't reset to 0 after reaching 24 hours. You can see this in the first and third output rows from the previous statement. To produce time-of-day values, enforce a 24-hour wraparound using a modulo operation before converting the seconds value back to a TIME value. The number of seconds in a day is 24 x 60 x 60, or 86,400, so to convert any seconds value s to lie within a 24-hour range, use the MOD⁠(⁠ ⁠ ⁠) function or the % modulo operator like this:

MOD(s,86400) s % 86400 s MOD 86400 

NOTE

The allowable range of a TIME column is -838:59:59 to 838:59:59 (that is, -3020399 to 3020399 seconds). However, the range of TIME expressions can be greater, so when you add times together, you can easily produce a result that lies outside this range and that cannot be stored as is into a TIME column.

The three expressions are equivalent. Applying the first of them to the time calculations from the preceding example produces the following result:

mysql> SELECT t1, t2,     -> MOD(TIME_TO_SEC(t1) + TIME_TO_SEC(t2), 86400)     ->   AS 't1 + t2 (in seconds)',     -> SEC_TO_TIME(MOD(TIME_TO_SEC(t1) + TIME_TO_SEC(t2), 86400))     ->   AS 't1 + t2 (as TIME)'     -> FROM time_val; +----------+----------+----------------------+-------------------+ | t1       | t2       | t1 + t2 (in seconds) | t1 + t2 (as TIME) | +----------+----------+----------------------+-------------------+ | 15:00:00 | 15:00:00 |                21600 | 06:00:00          | | 05:01:30 | 02:30:20 |                27110 | 07:31:50          | | 12:30:20 | 17:30:45 |                21665 | 06:01:05          | +----------+----------+----------------------+-------------------+ 

Adding to date or date-and-time values using basic units. By converting date or date-and-time values to basic units, you can shift them to produce other dates. For example, to shift a date forward or backward a week (seven days), use TO_DAYS⁠(⁠ ⁠ ⁠) and FROM_DAYS⁠(⁠ ⁠ ⁠):

mysql> SET @d = '2006-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 | +------------+---------------+---------------+ | 2006-01-01 | 2006-01-08    | 2005-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 = '2006-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 | +---------------------+-------------------+-------------------+ | 2006-01-01 12:30:45 | 2006-01-08        | 2005-12-25        | +---------------------+-------------------+-------------------+ 

To preserve the time with DATETIME or TIMESTAMP values, use UNIX_TIMESTAMP⁠(⁠ ⁠ ⁠) and FROM_UNIXTIME⁠(⁠ ⁠ ⁠) instead. The following statement shifts a DATETIME value forward and backward by an hour (3,600 seconds):

mysql> SET @dt = '2006-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   | +---------------------+---------------------+---------------------+ | 2006-01-01 09:00:00 | 2006-01-01 10:00:00 | 2006-01-01 08:00:00 | +---------------------+---------------------+---------------------+ 

The preceding technique requires that both your initial value and the resulting value lie in the allowable range for TIMESTAMP values (1970 to approximately 2037).