Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from 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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata