In MySQL, you can use the EXTRACT() function to extract parts from a date. For example, you can extract the year part, the month part, or the day part, etc. You can also extract parts from the time component, such as minutes, seconds, microseconds, etc.

This article contains examples to demonstrate.

Syntax

The syntax goes like this:

EXTRACT(unit FROM date)

Where unit is the date part that you want to extract, and date is the date value to extract that part from.

See the table at the bottom of this article for a list of valid unit specifiers.

Example 1

Here’s an example to demonstrate the function’s basic usage.

SELECT EXTRACT(YEAR FROM '2019-01-01') AS 'Result';

Result:

+--------+
| Result |
+--------+
|   2019 |
+--------+

Example 2 – More Date Parts

Here I extract the various date parts from a date.

SET @date = '2019-10-03';
SELECT 
    EXTRACT(DAY FROM @date) AS 'Day',
    EXTRACT(MONTH FROM @date) AS 'Month',
    EXTRACT(YEAR FROM @date) AS 'Year';

Result:

+------+-------+------+
| Day  | Month | Year |
+------+-------+------+
|    3 |    10 | 2019 |
+------+-------+------+

Example 3 – Extracting Time Parts

Here I extract various time parts from a date.

SET @date = '2019-10-03 12:35:05.123456';
SELECT 
    EXTRACT(HOUR FROM @date) AS 'Hours',
    EXTRACT(MINUTE FROM @date) AS 'Minutes',
    EXTRACT(SECOND FROM @date) AS 'Seconds',
    EXTRACT(MICROSECOND FROM @date) AS 'Microseconds';

Result:

+-------+---------+---------+--------------+
| Hours | Minutes | Seconds | Microseconds |
+-------+---------+---------+--------------+
|    12 |      35 |       5 |       123456 |
+-------+---------+---------+--------------+

Example 4 – Combining Unit Specifiers

You can also combine unit specifiers, like this:

SET @date = '2019-10-03 12:35:05.123456';
SELECT 
    EXTRACT(HOUR_MICROSECOND FROM @date) AS 'Result';

Result:

+--------------+
| Result       |
+--------------+
| 123505123456 |
+--------------+

This example returns everything from the hours part to the microseconds part.

A Database Example

Here’s an example of extracting the year and month from a column in a database:

USE sakila;
SELECT
  payment_date AS 'Date/Time',
  EXTRACT(YEAR_MONTH FROM payment_date) AS 'Year/Month'
FROM payment
WHERE payment_id = 1;

Result:

+---------------------+------------+
| Date/Time           | Year/Month |
+---------------------+------------+
| 2005-05-25 11:30:37 |     200505 |
+---------------------+------------+

Current Date/Time

Here, I extract the month part from a date generated with the CURDATE() function.

SELECT 
    CURDATE(),
    EXTRACT(MONTH FROM CURDATE());

Result:

+------------+-------------------------------+
| CURDATE()  | EXTRACT(MONTH FROM CURDATE()) |
+------------+-------------------------------+
| 2018-06-26 |                             6 |
+------------+-------------------------------+

In this example I extract the hour part from the current date and time (which is generated with the NOW() function).

SELECT 
    NOW(),
    EXTRACT(HOUR FROM NOW());

Result:

+---------------------+--------------------------+
| NOW()               | EXTRACT(HOUR FROM NOW()) |
+---------------------+--------------------------+
| 2018-06-26 09:01:46 |                        9 |
+---------------------+--------------------------+

Expected Values

The following table shows the valid unit values and their expected format.

unit Value	Expected expr Format
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	‘SECONDS.MICROSECONDS’
MINUTE_MICROSECOND	‘MINUTES:SECONDS.MICROSECONDS’
MINUTE_SECOND	‘MINUTES:SECONDS’
HOUR_MICROSECOND	‘HOURS:MINUTES:SECONDS.MICROSECONDS’
HOUR_SECOND	‘HOURS:MINUTES:SECONDS’
HOUR_MINUTE	‘HOURS:MINUTES’
DAY_MICROSECOND	‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’
DAY_SECOND	‘DAYS HOURS:MINUTES:SECONDS’
DAY_MINUTE	‘DAYS HOURS:MINUTES’
DAY_HOUR	‘DAYS HOURS’
YEAR_MONTH	‘YEARS-MONTHS’