5.16 Breaking Down Time Intervals into Components
5.16.1 Problem
You have a time interval represented
as a time, but you want the interval in terms of its components.
5.16.2 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.
5.16.3 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 | 02:30:20 | -02:31:10 | - | 2 | 31 | 10 |
| 12:30:20 | 17:30:45 | 05:00:25 | + | 5 | 0 | 25 |
+----------+----------+------------------+------+------+--------+--------+
But that's fairly messy, and attempting to do the
same thing using division and modulo operations is even messier. If
you happen to be issuing an interval-calculation query from within a
program, it's possible to avoid most of the clutter.
Use SQL to compute just the intervals in seconds, then use your API
language to break down each interval into its components. The
formulas should account for negative values and produce integer
values for each component. Here's an example
function time_components(
) written in Python that takes an
interval value in seconds and returns a four-element tuple containing
the sign of the value, followed by the hour, minute, and second
parts:
def time_components (time_in_secs):
if time_in_secs < 0:
sign = "-"
time_in_secs = -time_in_secs
else:
sign = ""
hours = int (time_in_secs / 3600)
minutes = int ((time_in_secs / 60)) % 60
seconds = time_in_secs % 60
return (sign, hours, minutes, seconds)
You might use time_components( ) within a program
like this:
query = "SELECT t1, t2, TIME_TO_SEC(t2) - TIME_TO_SEC(t1) FROM time_val"
cursor = conn.cursor ( )
cursor.execute (query)
for (t1, t2, interval) in cursor.fetchall ( ):
(sign, hours, minutes, seconds) = time_components (interval)
print "t1 = %s, t2 = %s, interval = %s%d h, %d m, %d s" \
% (t1, t2, sign, hours, minutes, seconds)
cursor.close ( )
The program produces the following output:
t1 = 15:00:00, t2 = 15:00:00, interval = 0 h, 0 m, 0 s
t1 = 05:01:30, t2 = 02:30:20, interval = -2 h, 31 m, 10 s
t1 = 12:30:20, t2 = 17:30:45, interval = 5 h, 0 m, 25 s
The preceding example illustrates a more general principle
that's often useful when issuing queries from a
program: it may be easier to deal with a calculation that is complex
to express in SQL by using a simpler query and postprocessing the
results using your API language.
|
