Recipe 6.8. Converting Between Temporal Data Types and Basic Units
ProblemYou have a function temporal value such as a time or date that you want to convert to basic units such as seconds or days. This is often useful or necessary for performing temporal arithmetic operations (Recipes Section 6.9 and Section 6.10). SolutionThe conversion method depends on the type of value to be converted:
DiscussionThe following discussion shows how to convert several types of temporal values to basic units and vice versa. Converting between times and secondsTIME values are specialized representations of a simpler unit (seconds), so you can convert back and forth from one to the other using 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 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.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( ) if you prefer integer values that have no fractional part: mysql> SELECT t1, -> TIME_TO_SEC(t1) AS 'seconds', -> FLOOR(TIME_TO_SEC(t1)/60) AS 'minutes', -> FLOOR(TIME_TO_SEC(t1)/(60*60)) AS 'hours', -> FLOOR(TIME_TO_SEC(t1)/(24*60*60)) AS 'days' -> FROM time_val; +----------+---------+---------+-------+------+ | t1 | seconds | minutes | hours | days | +----------+---------+---------+-------+------+ | 15:00:00 | 54000 | 900 | 15 | 0 | | 05:01:30 | 18090 | 301 | 5 | 0 | | 12:30:20 | 45020 | 750 | 12 | 0 | +----------+---------+---------+-------+------+ If you pass TIME_TO_SEC( ) a date-and-time value, it extracts the time part and discards the date. This provides yet another means of extracting times from DATETIME and TIMESTAMP values (in addition to those already discussed in Section 6.6): mysql> SELECT dt, -> TIME_TO_SEC(dt) AS 'time part in seconds', -> SEC_TO_TIME(TIME_TO_SEC(dt)) AS 'time part as TIME' -> FROM datetime_val; +---------------------+----------------------+-------------------+ | dt | time part in seconds | time part as TIME | +---------------------+----------------------+-------------------+ | 1970-01-01 00:00:00 | 0 | 00:00:00 | | 1987-03-05 12:30:15 | 45015 | 12:30:15 | | 1999-12-31 09:00:00 | 32400 | 09:00:00 | | 2000-06-04 15:45:30 | 56730 | 15:45:30 | +---------------------+----------------------+-------------------+ mysql> SELECT ts, -> TIME_TO_SEC(ts) AS 'time part in seconds', -> SEC_TO_TIME(TIME_TO_SEC(ts)) AS 'time part as TIME' -> FROM timestamp_val; +---------------------+----------------------+-------------------+ | ts | time part in seconds | time part as TIME | +---------------------+----------------------+-------------------+ | 1970-01-01 00:00:00 | 0 | 00:00:00 | | 1987-03-05 12:30:15 | 45015 | 12:30:15 | | 1999-12-31 09:00:00 | 32400 | 09:00:00 | | 2000-06-04 15:45:30 | 56730 | 15:45:30 | +---------------------+----------------------+-------------------+ Converting between dates and daysIf you have a date but want a value in days, or vice versa, use the TO_DAYS( ) and FROM_DAYS( ) functions. Date-and-time values also can be converted to days if you're willing to suffer loss of the time part. TO_DAYS( ) converts a date to the corresponding number of days, and FROM_DAYS( ) does the opposite: mysql> SELECT d, -> TO_DAYS(d) AS 'DATE to days', -> FROM_DAYS(TO_DAYS(d)) AS 'DATE to days to DATE' -> FROM date_val; +------------+--------------+----------------------+ | d | DATE to days | DATE to days to DATE | +------------+--------------+----------------------+ | 1864-02-28 | 680870 | 1864-02-28 | | 1900-01-15 | 693975 | 1900-01-15 | | 1987-03-05 | 725800 | 1987-03-05 | | 1999-12-31 | 730484 | 1999-12-31 | | 2000-06-04 | 730640 | 2000-06-04 | +------------+--------------+----------------------+ When using TO_DAYS( ), it's best to stick to the advice of the MySQL Reference Manual and avoid DATE values that occur before the beginning of the Gregorian calendar (1582). Changes in the lengths of calendar years and months prior to that date make it difficult to speak meaningfully of what the value of "day 0" might be. This differs from TIME_TO_SEC( ), where the correspondence between a TIME value and the resulting seconds value is obvious and has a meaningful reference point of 0 seconds. If you pass TO_DAYS( ) a date-and-time value, it extracts the date part and discards the time. This provides another means of extracting dates from DATETIME and TIMESTAMP values (in addition to those already discussed in Section 6.6): mysql> SELECT dt, -> TO_DAYS(dt) AS 'date part in days', -> FROM_DAYS(TO_DAYS(dt)) AS 'date part as DATE' -> FROM datetime_val; +---------------------+-------------------+-------------------+ | dt | date part in days | date part as DATE | +---------------------+-------------------+-------------------+ | 1970-01-01 00:00:00 | 719528 | 1970-01-01 | | 1987-03-05 12:30:15 | 725800 | 1987-03-05 | | 1999-12-31 09:00:00 | 730484 | 1999-12-31 | | 2000-06-04 15:45:30 | 730640 | 2000-06-04 | +---------------------+-------------------+-------------------+ mysql> SELECT ts, -> TO_DAYS(ts) AS 'date part in days', -> FROM_DAYS(TO_DAYS(ts)) AS 'date part as DATE' -> FROM timestamp_val; +---------------------+-------------------+-------------------+ | ts | date part in days | date part as DATE | +---------------------+-------------------+-------------------+ | 1970-01-01 00:00:00 | 719528 | 1970-01-01 | | 1987-03-05 12:30:15 | 725800 | 1987-03-05 | | 1999-12-31 09:00:00 | 730484 | 1999-12-31 | | 2000-06-04 15:45:30 | 730640 | 2000-06-04 | +---------------------+-------------------+-------------------+ Converting between date-and-time values and secondsFor DATETIME or TIMESTAMP values that lie within the range of the TIMESTAMP data type (from the beginning of 1970 through approximately 2037), the UNIX_TIMESTAMP( ) and FROM_UNIXTIME( ) functions convert to and from the number of seconds elapsed since the beginning of 1970. The conversion to seconds offers higher precision for date-and-time values than a conversion to days, at the cost of a more limited range of values for which the conversion may be performed (TIME_TO_SEC( ) is unsuitable for this because it discards the date): mysql> SELECT dt, -> UNIX_TIMESTAMP(dt) AS seconds, -> FROM_UNIXTIME(UNIX_TIMESTAMP(dt)) AS timestamp -> FROM datetime_val; +---------------------+-----------+---------------------+ | dt | seconds | timestamp | +---------------------+-----------+---------------------+ | 1970-01-01 00:00:00 | 21600 | 1970-01-01 00:00:00 | | 1987-03-05 12:30:15 | 541967415 | 1987-03-05 12:30:15 | | 1999-12-31 09:00:00 | 946652400 | 1999-12-31 09:00:00 | | 2000-06-04 15:45:30 | 960151530 | 2000-06-04 15:45:30 | +---------------------+-----------+---------------------+ The relationship between the "UNIX" in the function names and the fact that the applicable range of values begins with 1970 is that 1970-01-01 00:00:00 UTC marks the "Unix epoch." The epoch is time zero, or the reference point for measuring time in Unix systems. That being so, you may find it curious that the preceding example shows a UNIX_TIMESTAMP( ) value of 21600 for the first value in the datetime_val table. What's going on? Why isn't it 0? The apparent discrepancy is due to the fact that the MySQL server interprets the UNIX_TIMESTAMP( ) argument as a value in the client's local time zone and converts it to UTC. My server is in the U.S. Central time zone, six hours (21600 seconds) west of UTC. UNIX_TIMESTAMP( ) can convert DATE values to seconds, too. It treats such values as having an implicit time-of-day part of 00:00:00: mysql> SELECT -> CURDATE(), -> UNIX_TIMESTAMP(CURDATE()), -> FROM_UNIXTIME(UNIX_TIMESTAMP(CURDATE()))\G *************************** 1. row *************************** CURDATE(): 2006-05-30 UNIX_TIMESTAMP(CURDATE()): 1148965200 FROM_UNIXTIME(UNIX_TIMESTAMP(CURDATE())): 2006-05-30 00:00:00 |
