5.14.1 Problem
You want to add a given number of seconds to a time, or to add two time values.
5.14.2 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.
5.14.3 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 | 30:00:00 | | 05:01:30 | 02:30:20 | 07:31:50 | | 12:30:20 | 17:30:45 | 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 query. 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 86400, 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
The two expressions are equivalent. Applying the first of them to the time calculations from the preceding example produces the following result:
mysql> SELECT t1, t2, -> SEC_TO_TIME(MOD(TIME_TO_SEC(t1) + TIME_TO_SEC(t2), 86400)) AS 't1 + t2' -> FROM time_val; +----------+----------+----------+ | t1 | t2 | t1 + t2 | +----------+----------+----------+ | 15:00:00 | 15:00:00 | 06:00:00 | | 05:01:30 | 02:30:20 | 07:31:50 | | 12:30:20 | 17:30:45 | 06:01:05 | +----------+----------+----------+
|
Using the mysql Client Program
Writing MySQL-Based Programs
Record Selection Techniques
Working with Strings
Working with Dates and Times
Sorting Query Results
Generating Summaries
Modifying Tables with ALTER TABLE
Obtaining and Using Metadata
Importing and Exporting Data
Generating and Using Sequences
Using Multiple Tables
Statistical Techniques
Handling Duplicates
Performing Transactions
Introduction to MySQL on the Web
Incorporating Query Resultsinto Web Pages
Processing Web Input with MySQL
Using MySQL-Based Web Session Management
Appendix A. Obtaining MySQL Software
Appendix B. JSP and Tomcat Primer
Appendix C. References