Recipe 6.6. Extracting Parts of Dates or Times

Problem

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

Solution

You have several options:

• Invoke a function specifically intended for extracting part of a temporal value, such as MONTH⁠(⁠ ⁠ ⁠) or MINUTE⁠(⁠ ⁠ ⁠). This is usually the fastest method for component extraction if you need only a single component of a value.

• 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.

• Treat a temporal value as a string and use a function such as LEFT⁠(⁠ ⁠ ⁠) or MID⁠(⁠ ⁠ ⁠) to extract substrings corresponding to the desired part of the value.

Discussion

The following discussion shows different ways to extract parts of temporal values.

Decomposing dates or times using component-extraction functions

MySQL includes many functions for extracting date or time parts from temporal values. For example, DATE⁠(⁠ ⁠ ⁠) or TIME⁠(⁠ ⁠ ⁠) extracts the date or time part of temporal values:

mysql> SELECT dt, DATE(dt), TIME(dt) FROM datetime_val; +---------------------+------------+----------+ | dt                  | DATE(dt)   | TIME(dt) | +---------------------+------------+----------+ | 1970-01-01 00:00:00 | 1970-01-01 | 00:00:00 | | 1987-03-05 12:30:15 | 1987-03-05 | 12:30:15 | | 1999-12-31 09:00:00 | 1999-12-31 | 09:00:00 | | 2000-06-04 15:45:30 | 2000-06-04 | 15:45:30 | +---------------------+------------+----------+ 

Some of the other component-extraction functions 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 the temporal value to which you apply them. Other date component-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 | +------------+------------+--------------+------------+ 

  
  

EXtrACT⁠(⁠ ⁠ ⁠) is another function for obtaining individual parts of temporal values:

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 from the value should be a unit specifier such as YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND. (Check the MySQL Reference Manual for the full list.) Note that each unit specifier is given in singular form, not plural.

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

The extraction functions shown in this recipe 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  | +------------+------+-------+-----------+------+----------+ | 2006-06-03 | 2006 |     6 | June      |    3 | Saturday | +------------+------+-------+-----------+------+----------+ 

Similarly, to obtain the current hour, minute, or second, pass 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 | +---------------------+------+--------+--------+ | 2006-06-03 09:45:32 |    9 |     45 |     32 | +---------------------+------+--------+--------+ 

Decomposing dates or times using formatting functions

The DATE_FORMAT⁠(⁠ ⁠ ⁠) and TIME_FORMAT⁠(⁠ ⁠ ⁠) functions reformat date and time values. By specifying appropriate format strings, you can 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 enable 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  | | 1987-03-05 12:30:15 | 1987-03-05 | 12:30:15  | | 1999-12-31 09:00:00 | 1999-12-31 | 09:00:00  | | 2000-06-04 15:45:30 | 2000-06-04 | 15:45:30  | +---------------------+------------+-----------+ 

One advantage of using formatting functions is that you can display the extracted values in a different form from that in which they're present in the original values. If you want to present a date differently from CCYY-MM-DD format or present a time without the seconds part, that's easy to do:

mysql> SELECT ts,     -> DATE_FORMAT(ts,'%M %e, %Y') AS 'descriptive date',     -> TIME_FORMAT(ts,'%H:%i') AS 'hours/minutes'     -> FROM timestamp_val; +---------------------+-------------------+---------------+ | ts                  | descriptive date  | hours/minutes | +---------------------+-------------------+---------------+ | 1970-01-01 00:00:00 | January 1, 1970   | 00:00         | | 1987-03-05 12:30:15 | March 5, 1987     | 12:30         | | 1999-12-31 09:00:00 | December 31, 1999 | 09:00         | | 2000-06-04 15:45:30 | June 4, 2000      | 15:45         | +---------------------+-------------------+---------------+ 

Decomposing dates or times using string functions

The discussion in this section thus far has shown how to extract components of temporal values using functions such as YEAR⁠(⁠ ⁠ ⁠), MONTH⁠(⁠ ⁠ ⁠), and DATE_FORMAT⁠(⁠ ⁠ ⁠). If you pass a date or time value to a string function, MySQL treats it as a string, so string functions provide another way to decompose temporal values. This means that you can extract pieces of temporal values by using functions such as LEFT⁠(⁠ ⁠ ⁠) or MID⁠(⁠ ⁠ ⁠) to pull out substrings:

mysql> SELECT dt,     -> LEFT(dt,4) AS year,     -> MID(dt,9,2) AS day,     -> RIGHT(dt,2) AS second     -> FROM datetime_val; +---------------------+------+------+--------+ | dt                  | year | day  | second | +---------------------+------+------+--------+ | 1970-01-01 00:00:00 | 1970 | 01   | 00     | | 1987-03-05 12:30:15 | 1987 | 05   | 15     | | 1999-12-31 09:00:00 | 1999 | 31   | 00     | | 2000-06-04 15:45:30 | 2000 | 04   | 30     | +---------------------+------+------+--------+ 

You can obtain the entire date or time part from DATETIME or TIMESTAMP values using string-extraction functions such as LEFT⁠(⁠ ⁠ ⁠) or RIGHT⁠(⁠ ⁠ ⁠):

mysql> SELECT dt,     -> LEFT(dt,10) AS date,     -> RIGHT(dt,8) AS time     -> FROM datetime_val; +---------------------+------------+----------+ | dt                  | date       | time     | +---------------------+------------+----------+ | 1970-01-01 00:00:00 | 1970-01-01 | 00:00:00 | | 1987-03-05 12:30:15 | 1987-03-05 | 12:30:15 | | 1999-12-31 09:00:00 | 1999-12-31 | 09:00:00 | | 2000-06-04 15:45:30 | 2000-06-04 | 15:45:30 | +---------------------+------------+----------+ mysql> SELECT ts,     -> LEFT(ts,10) AS date,     -> RIGHT(ts,8) AS time     -> FROM timestamp_val; +---------------------+------------+----------+ | ts                  | date       | time     | +---------------------+------------+----------+ | 1970-01-01 00:00:00 | 1970-01-01 | 00:00:00 | | 1987-03-05 12:30:15 | 1987-03-05 | 12:30:15 | | 1999-12-31 09:00:00 | 1999-12-31 | 09:00:00 | | 2000-06-04 15:45:30 | 2000-06-04 | 15:45:30 | +---------------------+------------+----------+ 

Decomposition of temporal values with string functions is subject to a couple of constraints that component extraction and reformatting functions are not bound by:

• To use a substring function such as LEFT⁠(⁠ ⁠ ⁠), MID⁠(⁠ ⁠ ⁠), or RIGHT⁠(⁠ ⁠ ⁠), you must have fixed-length strings. Although MySQL interprets the value 1987-1-1 as 1987-01-01 if you insert it into a DATE column, using RIGHT('1987-1-1',2) to extract the day part will not work. For values that have variable-length substrings, you may be able to use SUBSTRING_INDEX⁠(⁠ ⁠ ⁠) instead. Alternatively, if your values are close to ISO format, perhaps you can standardize them to ISO format using the techniques described in Section 6.18. This converts them to have fixed-length substrings before you attempt to extract subparts.

• String functions cannot be used to obtain values that don't correspond to substrings of a date value, such as the day of the week or the day of the year.