Adding a Temporal Interval to a Time
Problem
You want to add a given number of seconds to a time, or to add two time values.
Solution
Use TIME_TO_SEC( ) as necessary to make sure all values are
represented in seconds, then add them. The result will be in seconds;
use SEC_TO_TIME( ) if you want to convert back to a time value.
Discussion
The primary tools for performing time arithmetic are
TIME_TO_SEC( ) and SEC_TO_TIME( ), which convert between TIME values and
seconds. 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 query 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 the time_val
table:
mysql>SELECT t1, t2,->SEC_TO_TIME(TIME_TO_SEC(t1) + TIME_TO_SEC(t2)) AS 't1 + t2'->FROM time_val;+----------+----------+----------+ | t1 | t2 | t1 + t2 | +----------+----------+----------+ | 15:00:00 | 15:00:00 ...