October 2002
Intermediate to advanced
1024 pages
27h 26m
English
You have a time value but you want a value in seconds, or vice versa.
TIME values are specialized representations of a
simpler unit—seconds—and you can convert back and forth
from one to the other using TIME_TO_SEC( )
and SEC_TO_TIME( ).
TIME_TO_SEC( ) converts a TIME
value to the equivalent number of seconds, and SEC_TO_TIME( ) does the opposite. The following query demonstrates a
simple conversion in both directions:
mysql>SELECT t1,->TIME_TO_SEC(t1) AS 'TIME to seconds',->SEC_TO_TIME(TIME_TO_SEC(t1)) AS 'TIME to seconds to TIME'->FROM time_val;+----------+-----------------+-------------------------+ | t1 | TIME to seconds | TIME to seconds to TIME | +----------+-----------------+-------------------------+ | 15:00:00 | 54000 | 15:00:00 | | 05:01:30 | 18090 | 05:01:30 | | 12:30:20 | 45020 | 12:30:20 | +----------+-----------------+-------------------------+
To express time values as minutes, hours, or days, perform the appropriate divisions:
mysql>SELECT t1,->TIME_TO_SEC(t1) AS 'seconds',->TIME_TO_SEC(t1)/60 AS 'minutes',->TIME_TO_SEC(t1)/(60*60) AS 'hours',->TIME_TO_SEC(t1)/(24*60*60) AS 'days'->FROM time_val;+----------+---------+---------+-------+------+ | t1 | seconds | minutes | hours | days | +----------+---------+---------+-------+------+ | 15:00:00 | 54000 | 900.00 | 15.00 | 0.62 | | 05:01:30 | 18090 | 301.50 | 5.03 | 0.21 | | 12:30:20 | 45020 | 750.33 | 12.51 | 0.52 | +----------+---------+---------+-------+------+ ...