MySQL EXTRACT() Function
The EXTRACT() function in MySQL allows you to retrieve specific parts (components) from a date or datetime value. This is very useful for working with dates and times and for performing various date/time-based operations or for creating reports.
EXTRACT(): Definition and Usage
EXTRACT() lets you pull out individual components of a date or time, such as the year, month, day, hour, minute, second, etc. You specify the part you want and the date or datetime value; the function returns that component as a number.
Syntax
Syntax
EXTRACT(part FROM date)
Parameter Values
| Parameter | Description |
|---|---|
part |
The part of the date or time to extract. Options: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH. This is required. |
date |
The date or datetime value. This is required. |
Examples
Extracting the Month
This example extracts the month (6) from the date "2017-06-15".
Syntax
SELECT EXTRACT(MONTH FROM "2017-06-15");
Output
6
Extracting Other Date/Time Parts
These examples show how to extract different components.
Syntax
SELECT EXTRACT(WEEK FROM "2017-06-15"); -- Week number
SELECT EXTRACT(MINUTE FROM "2017-06-15 09:34:21"); -- Minute
SELECT EXTRACT(YEAR_MONTH FROM "2017-06-15 09:34:21"); -- Year and month (YYYYMM format)
Output
24
34
201706