Decomposing Dates or Times Using Component-Extraction Functions

2017-11-03 09:05:05

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.

Obtaining the Current Year, Month, Day, Hour, Minute, or Second

Категории