Working with Dates and Times - MySQL - page 202

Discussion
The following discussion shows how to convert several types of temporal values to basic
units and vice versa.
Converting between times and seconds
TIME values are specialized representations of a simpler unit (seconds). To convert from
one to the other, use the TIME_TO_SEC() and SEC_TO_TIME() functions.
TIME_TO_SEC() converts a TIME value to the equivalent number of seconds, and
SEC_TO_TIME() does the opposite. The following statement demonstrates a simple con‐
version 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.0000 | 15.0000 | 0.6250 |
| 05:01:30 | 18090 | 301.5000 | 5.0250 | 0.2094 |
| 12:30:20 | 45020 | 750.3333 | 12.5056 | 0.5211 |
+----------+---------+----------+---------+--------+
Use FLOOR() on the division results if you prefer integer values that have no fractional
part.
If you pass TIME_TO_SEC() a date-and-time value, it extracts the time part and discards
the date. This provides another means of extracting times from DATETIME (or TIME
STAMP ) values, in addition to those already discussed in Recipe 6.8 :
mysql> SELECT dt,
-> TIME_TO_SEC(dt) AS 'time part in seconds',
-> SEC_TO_TIME(TIME_TO_SEC(dt)) AS 'time part as TIME'