Breaking Down Time Intervals into Components
Problem
You have a time interval represented as a time, but you want the interval in terms of its components.
Solution
Decompose the interval with the HOUR( )
,
MINUTE( )
, and SECOND( )
functions. If the calculation is complex in SQL and
you’re using the interval within a program, it may
be easier to use your programming language to perform the equivalent
math.
Discussion
To express a time interval in terms of its constituent hours,
minutes, and seconds values, calculate time interval subparts in SQL
using the HOUR( )
, MINUTE( )
,
and SECOND( )
functions. (Don’t
forget that if your intervals may be negative, you need to take that
into account.) For example, to determine the components of the
intervals between the t1
and t2
columns in the time_val
table, the following SQL
statement does the trick:
mysql>SELECT t1, t2,
->SEC_TO_TIME(TIME_TO_SEC(t2) - TIME_TO_SEC(t1)) AS 'interval as TIME',
->IF(SEC_TO_TIME(TIME_TO_SEC(t2) >= TIME_TO_SEC(t1)),'+','-') AS sign,
->HOUR(SEC_TO_TIME(TIME_TO_SEC(t2) - TIME_TO_SEC(t1))) AS hour,
->MINUTE(SEC_TO_TIME(TIME_TO_SEC(t2) - TIME_TO_SEC(t1))) AS minute,
->SECOND(SEC_TO_TIME(TIME_TO_SEC(t2) - TIME_TO_SEC(t1))) AS second
->FROM time_val;
+----------+----------+------------------+------+------+--------+--------+ | t1 | t2 | interval as TIME | sign | hour | minute | second | +----------+----------+------------------+------+------+--------+--------+ | 15:00:00 | 15:00:00 | 00:00:00 | + | 0 | 0 | 0 | | 05:01:30 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.