Working with Dates and Times - MySQL - page 195

Solution
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. Alternatively, 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.

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 and time subpart extraction. For
example, DATE() and TIME() extract the date and time components 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 |
| 1999-12-31 09:00:00 | 1999-12-31 | 09:00:00 |
| 2000-06-04 15:45:30 | 2000-06-04 | 15:45:30 |
| 2017-03-16 12:30:15 | 2017-03-16 | 12:30:15 |
+---------------------+------------+----------+
The following table shows some several component-extraction functions; 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 , DA
TETIME , 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 name (Sunday..Saturday)

Day of week (1..7 for Sunday..Saturday)

DAYOFWEEK()

Day of week (0..6 for Monday..Sunday)

WEEKDAY()

Day of year (1..366)

DAYOFYEAR()

Hour of time (0..23)

HOUR()

Minute of time (0..59)

MINUTE()

Second of time (0..59)

SECOND()

Varies

EXTRACT()