Datetime Conversions: MySQL
MySQL implements a variety of datetime conversion functions, including some in support of Unix timestamps. The available functions are described in the following subsections.
Date and Time Elements
MySQL supports the following functions to return specific date and time elements:
For example, to return the current date in text form, specify:
SELECT CONCAT(DAYOFMONTH(CURRENT_DATE), '-', MONTHNAME(CURRENT_DATE), '-', YEAR(CURRENT_DATE)); 2-January-2004
For functions taking a
argument, you can specify whether weeks begin on Sunday
first = 0) or on Monday
first = 1).
TO_DAYS and FROM_DAYS
Use TO_DAYS to convert a date into the number of days since the beginning of the Christian calendar (1-Jan-0001 is considered day 1):
SELECT TO_DAYS(CURRENT_DATE); 731947
Use FROM_DAYS to convert in the reverse direction:
SELECT FROM_DAYS(731947); 2004-01-02
These functions are designed for use only with Gregorian dates, which begin on 15-Oct-1582. TO_DAYS and FROM_DAYS functions will not return correct results for earlier dates.
Unix Timestamp Support
The following functions convert to and from Unix timestamps:
Returns a Unix timestamp, which is an unsigned integer with the number of seconds since 1-Jan-1970. With no argument, ...