MySQL Cookbook

Adding a Temporal Interval to a Date

Problem

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

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( ).

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.^[31] Each function takes a date value d and an interval, expressed using the following syntax:

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