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:
mysql> SELECT CURDATE( ), DATE_ADD(CURDATE( ),INTERVAL 3 DAY); +------------+------------------------------------+ | CURDATE( ) | DATE_ADD(CURDATE( ),INTERVAL 3 DAY) | +------------+------------------------------------+ | 2002-07-15 | 2002-07-18 | +------------+------------------------------------+
mysql> SELECT CURDATE( ), DATE_SUB(CURDATE( ),INTERVAL 7 DAY); +------------+------------------------------------+ | CURDATE( ) | DATE_SUB(CURDATE( ),INTERVAL 7 DAY) | +------------+------------------------------------+ | 2002-07-15 | 2002-07-08 | +------------+------------------------------------+
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 | +---------------------+----------------------------------+
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).
Using the mysql Client Program
Writing MySQL-Based Programs
Record Selection Techniques
Working with Strings
Working with Dates and Times
Sorting Query Results
Generating Summaries
Modifying Tables with ALTER TABLE
Obtaining and Using Metadata
Importing and Exporting Data
Generating and Using Sequences
Using Multiple Tables
Statistical Techniques
Handling Duplicates
Performing Transactions
Introduction to MySQL on the Web
Incorporating Query Resultsinto Web Pages
Processing Web Input with MySQL
Using MySQL-Based Web Session Management
Appendix A. Obtaining MySQL Software
Appendix B. JSP and Tomcat Primer
Appendix C. References