MySQL Cookbook

Decomposing Dates and Times Using Formatting Functions

Problem

You want to obtain just a part of a date or a time.

Solution

Use a formatting function such as DATE_FORMAT( ) or TIME_FORMAT( ) with a format string that includes a specifier for the part of the value you want to obtain.

Discussion

MySQL provides several options for decomposing dates or times to obtain their component values. The DATE_FORMAT( ) and TIME_FORMAT( ) functions provide one way to extract individual parts of temporal values:

mysql> SELECT dt,
    -> DATE_FORMAT(dt,'%Y') AS year,
    -> DATE_FORMAT(dt,'%d') AS day,
    -> TIME_FORMAT(dt,'%H') AS hour,
    -> TIME_FORMAT(dt,'%s') AS second
    -> FROM datetime_val;
+---------------------+------+------+------+--------+
| dt                  | year | day  | hour | second |
+---------------------+------+------+------+--------+
| 1970-01-01 00:00:00 | 1970 | 01   | 00   | 00     |
| 1987-03-05 12:30:15 | 1987 | 05   | 12   | 15     |
| 1999-12-31 09:00:00 | 1999 | 31   | 09   | 00     |
| 2000-06-04 15:45:30 | 2000 | 04   | 15   | 30     |
+---------------------+------+------+------+--------+

Formatting functions allow you to extract more than one part of a value. For example, to extract the entire date or time from DATETIME values, do this:

mysql> SELECT dt,
    -> DATE_FORMAT(dt,'%Y-%m-%d') AS 'date part',
    -> TIME_FORMAT(dt,'%T') AS 'time part'
    -> FROM datetime_val; +---------------------+------------+-----------+ | dt | date part | time part | +---------------------+------------+-----------+ | 1970-01-01 00:00:00 | 1970-01-01 | 00:00:00 ...