Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

Converting Between Date-and-Time Values and Seconds

Problem

You have a date-and-time value but want a value in seconds, or vice versa.

Solution

The UNIX_TIMESTAMP( ) and FROM_UNIXTIME( ) functions convert DATETIME or TIMESTAMP values in the range from 1970 through approximately 2037 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.

Discussion

When working with date-and-time values, you can use TO_DAYS( ) and FROM_DAYS( ) to convert date values to days and back to dates, as shown in the previous section. For values that occur no earlier than 1970-01-01 00:00:00 GMT and no later than approximately 2037, it’s possible to achieve higher precision by converting to and from seconds.[29] UNIX_TIMESTAMP( ) converts date-and-time values in this range to the number of seconds elapsed since the beginning of 1970, and FROM_UNIXTIME( ) does the opposite:

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 | | ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata