MySQL Date and Time Conversion Functions
Date and Time in MySQL – Part 5
Forward: In this part of the series, I explain MySQL Date and Time Conversion Functions.
By: Chrysanthus Date Published: 4 Sep 2012
Introduction
The SEC_TO_TIME() Function
The time format is, HH:MM:SS. Any number of seconds, especially when greater than 60 can be represented as a number of hours, minutes and seconds in the time format, HH:MM:SS. So the SEC_TO_TIME() function converts a number of seconds to hours, minutes and seconds. The syntax is:
SEC_TO_TIME(seconds)
The following example converts 4573 seconds to 1hour, 16 minutes and 13 seconds.
SELECT sec_to_time(4573);
The return string value is, 01:16:13 .
The TIME_TO_SEC() Function
You can convert time into a total number of seconds, as the total of hours, minutes and seconds gives you an accumulated number of seconds. The TIME_TO_SEC() function does the trick. The syntax is:
TIME_TO_SEC(time)
This function does the opposite of the above function. The following statement illustrates the use of the function:
SELECT time_to_sec('01:16:13');
Do not forget to place the time in quotes as argument in the function call. The return value is 4573 seconds (the unit is not returned), everything opposite to what we had before, as expected.
The TO_SECONDS() Function
It is not only time that can be converted to seconds. A date or datetime can also be converted to seconds. The TO_SECONDS() function is used for that. The syntax is:
TO_SECONDS(expr)
The following two statements illustrate this:
SELECT to_seconds('2012-06-06 09:17:35');
SELECT to_seconds('2012-06-06');
The return value for the first statement is, 63506193455 and that for the second is, 63506160000 (accumulated number of seconds).
Beginning from the year, 0000 (i.e. date 0000-00-00) MySQL accumulates days, which can be converted to a date. The FROM_DAYS() function is used for this. The syntax:
FROM_DAYS(N)
where N is the number of days from the year, 0000. The following statements illustrate this:
SELECT from_days(730269);
SELECT from_days(400);
Note: a number does not go into quotes in parentheses. For the first statement, the result is, 1999-05-30 and for the second the result is, 0001-02-04.
The TO_DAYS() Function
This function does the opposite of the FROM_DAYS() function. The syntax is:
TO_DAYS(date)
The following two statements illustrate this with the opposite values of the above:
SELECT to_days('1999-05-30');
SELECT to_days('0001-02-04');
Remember, the string date goes into quotes of the parentheses. The date '1999-05-30' gives 730269 days and the date '0001-02-04' gives 400 days (units are not returned).
Note: the two functions, FROM DAYS and TO_DAYS are not very reliable when dealing with actual dates in history before the year 1582. However, they are OK for theoretical analysis before 1582.
A time is used in a time zone. If you can take a snapshot of the world (globe) then astronomers will tell you that the map has vertical strips called time zones. At the moment the snapshot was taken, time is different in different time zones, differing by 1 hour as you scan the map from east to west, from one time zone to the next. The reference time or reference time-zone is the time (time zone) of Britain. That reference time is called the UTC or GMT time.
Wherever you are in the world, MySQL can convert the time read from your computer to another time zone. The CONVERT_TZ() function does the trick. The syntax is:
CONVERT_TZ(dt,from_tz,to_tz)
where dt is the datetime value, from_tz is the time zone converting from, and to_tz is the timezone converting to.
Time zones have names. The one through Britain is called GTM and the one through Moscow (Russia) is Europe/Moscow; there is another one called MET. The following example copied from the specification converts datetime from the GMT time zone to the MET time zone.
SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
My MySQL server does not support this function and I will not talk about it any further.
That is it for this part of the series. We stop here and continue in the next part of the series.
Chrys