Extracting Hours, Minutes, and Seconds
HOUR(time)
MINUTE(time)
SECOND(time)These functions can be used to easily extract the hour, minute, and second elements of a given time.
HOUR returns a number in the range 0 to 23. For example, HOUR(18:10:27) returns 18. You can also
do the following:
mysql> SELECT HOUR(NOW());
produces the following:
+-------------+
| HOUR(NOW()) |
+-------------+
| 18 |
+-------------+
MINUTE and SECOND work in the same way, and both return a number in the range 0 to 59.
Days Since 1 A.D.
TO_DAYS(date)
FROM_DAYS(number)
TO_DAYS(date) as parameter and returns the number of days since the first day in the year 1 A.D.
(there was no year 0). Thus, TO_DAYS('2000-01-01') returns the number 730485.
The opposite effect is obtained by using FROM_DAYS(number). SELECT FROM_DAYS(1000000)
would return 2737-11-28 as the millionth day after that original time.
These functions are only intended to be used on dates after 1582, when the Gregorian Calendar was
introduced.
Seconds Since the Beginning of the Day
TIME_TO_SEC(time)
SEC_TO_TIME(seconds)
TIME_TO_SEC(time) returns the time in seconds (the number of seconds since the beginning of that
day).
For example, TIME_TO_SEC('01.00.00') returns 3600 , and you can also do the following:
mysql> SELECT TIME_TO_SEC(110);which results in
+------------------+
| TIME_TO_SEC(110) |
+------------------+
| 70 |
+------------------+because 110 (numeric) is interpreted by MySQL as 1 minute, 10 seconds.
SEC_TO_TIME(seconds) does the opposite, converting the number of seconds from the beginning of
the day into HH:MM:SS or HHMMSS format. You can do the following:
mysql> SELECT SEC_TO_TIME(70);which results in
+-----------------+
| SEC_TO_TIME(70) |
+-----------------+
| 00:01:10 |