MySQL Cookbook, 2nd Edition

Converting Between Temporal Data Types and Basic Units

Problem

You 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 and ).

Solution

The conversion method depends on the type of value to be converted:

• To convert between time values and seconds, use the TIME_TO_SEC() and SEC_TO_TIME() functions.

• To convert between date values and days, use the TO_DAYS() and FROM_DAYS() functions.

• To convert between date-and-time values and seconds, use the UNIX_TIMESTAMP() and FROM_UNIXTIME() functions.

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), 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 | | ...