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.

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.

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

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.