MySQL Cookbook

5.6 Decomposing Dates or Times Using Component-Extraction Functions

5.6.1 Problem

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

5.6.2 Solution

Invoke a function specifically intended for extracting part of a temporal value, such as MONTH( ) or MINUTE( ). For obtaining single components of temporal values, these functions are faster than using DATE_FORMAT( ) for the equivalent operation.

5.6.3 Discussion

MySQL includes many functions for extracting date or time parts from temporal values. Some of these are shown in the following list; consult the MySQL Reference Manual for a complete list. The date-related functions work with DATE, DATETIME, or TIMESTAMP values. The time-related functions work with TIME, DATETIME, or TIMESTAMP values.

Function	Return Value
YEAR( )	Year of date
MONTH( )	Month number (1..12)
MONTHNAME( )	Month name (January..December)
DAYOFMONTH( )	Day of month (1..31)
DAYNAME( )	Day of week (Sunday..Saturday)
DAYOFWEEK( )	Day of week (1..7 for Sunday..Saturday)
WEEKDAY( )	Day of week (0..6 for Monday..Sunday)
DAYOFYEAR( )	Day of year (1..366)
HOUR( )	Hour of time (0..23)
MINUTE( )	Minute of time (0..59)
SECOND( )	Second of time (0..59)

Here's an example:

mysql> SELECT dt,
    -> YEAR(dt), DAYOFMONTH(dt),
    -> HOUR(dt), SECOND(dt)
    -> FROM datetime_val;
+---------------------+----------+----------------+----------+------------+
| dt                  | YEAR(dt) | DAYOFMONTH(dt) | HOUR(dt) | SECOND(dt) |
+---------------------+----------+----------------+----------+------------+
| 1970-01-01 00:00:00 |     1970 |              1 |        0 |          0 |
| 1987-03-05 12:30:15 |     1987 |              5 |       12 |         15 |
| 1999-12-31 09:00:00 |     1999 |             31 |        9 |          0 |
| 2000-06-04 15:45:30 |     2000 |              4 |       15 |         30 |
+---------------------+----------+----------------+----------+------------+

Functions such as YEAR( ) or DAYOFMONTH( ) extract values that have an obvious correspondence to a substring of date values. Some date extraction functions provide access to values that have no such correspondence. One is the day-of-year value:

mysql> SELECT d, DAYOFYEAR(d) FROM date_val;
+------------+--------------+
| d          | DAYOFYEAR(d) |
+------------+--------------+
| 1864-02-28 |           59 |
| 1900-01-15 |           15 |
| 1987-03-05 |           64 |
| 1999-12-31 |          365 |
| 2000-06-04 |          156 |
+------------+--------------+

Another is the day of the week, which can be obtained either by name or by number:

• DAYNAME( ) returns the complete day name. There is no function for returning the three-character name abbreviation, but you can get it easily by passing the full name to LEFT( ):

  mysql> SELECT d, DAYNAME(d), LEFT(DAYNAME(d),3) FROM date_val;
  +------------+------------+--------------------+
  | d          | DAYNAME(d) | LEFT(DAYNAME(d),3) |
  +------------+------------+--------------------+
  | 1864-02-28 | Sunday     | Sun                |
  | 1900-01-15 | Monday     | Mon                |
  | 1987-03-05 | Thursday   | Thu                |
  | 1999-12-31 | Friday     | Fri                |
  | 2000-06-04 | Sunday     | Sun                |
  +------------+------------+--------------------+

• To get the day of the week as a number, use DAYOFWEEK( ) or WEEKDAY( )—but pay attention to the range of values each function returns. DAYOFWEEK( ) returns values from 1 to 7, corresponding to Sunday through Saturday. WEEKDAY( ) returns values from 0 to 6, corresponding to Monday through Sunday.

  mysql> SELECT d, DAYNAME(d), DAYOFWEEK(d), WEEKDAY(d) FROM date_val;
  +------------+------------+--------------+------------+
  | d          | DAYNAME(d) | DAYOFWEEK(d) | WEEKDAY(d) |
  +------------+------------+--------------+------------+
  | 1864-02-28 | Sunday     |            1 |          6 |
  | 1900-01-15 | Monday     |            2 |          0 |
  | 1987-03-05 | Thursday   |            5 |          3 |
  | 1999-12-31 | Friday     |            6 |          4 |
  | 2000-06-04 | Sunday     |            1 |          6 |
  +------------+------------+--------------+------------+

Another way to obtain individual parts of temporal values is to use the EXTRACT( ) function:

mysql> SELECT dt,
    -> EXTRACT(DAY FROM dt),
    -> EXTRACT(HOUR FROM dt)
    -> FROM datetime_val;
+---------------------+----------------------+-----------------------+
| dt                  | EXTRACT(DAY FROM dt) | EXTRACT(HOUR FROM dt) |
+---------------------+----------------------+-----------------------+
| 1970-01-01 00:00:00 |                    1 |                     0 |
| 1987-03-05 12:30:15 |                    5 |                    12 |
| 1999-12-31 09:00:00 |                   31 |                     9 |
| 2000-06-04 15:45:30 |                    4 |                    15 |
+---------------------+----------------------+-----------------------+

The keyword indicating what to extract should be a unit specifier such as YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND. The EXTRACT( ) function is available as of MySQL 3.23.0.

Obtaining the Current Year, Month, Day, Hour, Minute, or Second The extraction functions shown in this section can be applied to CURDATE( ) or NOW( ) to obtain the current year, month, day, or day of week: mysql> SELECT CURDATE( ), YEAR(CURDATE( )) AS year, -> MONTH(CURDATE( )) AS month, MONTHNAME(CURDATE( )) AS monthname, -> DAYOFMONTH(CURDATE( )) AS day, DAYNAME(CURDATE( )) AS dayname; +------------+------+-------+-----------+------+---------+ | CURDATE( ) | year | month | monthname | day | dayname | +------------+------+-------+-----------+------+---------+ | 2002-07-15 | 2002 | 7 | July | 15 | Monday | +------------+------+-------+-----------+------+---------+ Similarly, you can obtain the current hour, minute, and second by passing CURTIME( ) or NOW( ) to a time-component function: mysql> SELECT NOW( ), HOUR(NOW( )) AS hour, -> MINUTE(NOW( )) AS minute, SECOND(NOW( )) AS second; +---------------------+------+--------+--------+ | NOW( ) | hour | minute | second | +---------------------+------+--------+--------+ | 2002-07-15 11:21:12 | 11 | 21 | 12 | +---------------------+------+--------+--------+

5.6.4 See Also

The functions discussed in this recipe provide single components of temporal values. If you want to produce a value consisting of multiple components from a given value, it may be more convenient to use DATE_FORMAT( ). See Recipe 5.5.