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.
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.
The following discussion shows several ways to add temporal values.
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,INTERVALval unit
) DATE_SUB(d,INTERVALval unit
)
The +
INTERVAL
and -
INTERVAL
operators are similar:
d + INTERVALval unit
d - INTERVALval 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,INTERVALinterval
unit
)
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 Converting Between Temporal Data Types and Basic Units.
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 × 60 × 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 ×
60 × 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
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).
18.116.67.70