Database Reference
In-Depth Information
| 2014-02-20 18:07:06 | 2014-02-19 06:07:06 |
+---------------------+----------------------------------+
TIMESTAMPADD()
is an alternative function for adding intervals to date or date-and-time
values. Its arguments are similar to those for
DATE_ADD()
, and 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 informa‐
tion about the applicable functions, see
Recipe 6.10
.
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 and convert the result back to a
TIME
. For example, two hours is
7,200 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 |
