MySQL Cookbook

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