SQL Pocket Guide, 3rd Edition

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:

DAYOFWEEK(date)
WEEKDAY(date)
DAYOFMONTH(date)
DAYOFYEAR(date)
MONTH(date)
DAYNAME(date)
MONTHNAME(date)
QUARTER(date)
WEEK(date)
WEEK(date, first)
YEAR(date)
YEARWEEK(date)
YEARWEEK(date, first)
HOUR(time)
MINUTE(time)
SECOND(time)

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 first 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:

UNIX_TIMESTAMP([ date ])

Returns a Unix timestamp, which is an unsigned integer with the number of seconds since 1-Jan-1970. With no argument, ...