MySQL Cookbook

Decomposing Dates or Times Using Component-Extraction Functions

Problem

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

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.

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) | +---------------------+----------+----------------+----------+------------+ | ...