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.