The EXTRACT() function in MySQL is used to retrieve specific parts of a date or time value. It allows you to extract components such as the year, month, day, hour, minute, second, etc., from a given datetime or date value.
Definition and Usage
- The
EXTRACT()function extracts a specific part of a date or datetime. - It can be used to extract various components like year, month, day, hour, minute, second, and more.
Syntax
EXTRACT(part FROM date)
- part: Required. The part to extract from the date. Some possible values include:
MICROSECOND,SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER,YEAR, etc.
- date: Required. The date or datetime value from which the part will be extracted.
Examples
Example 1: Extract the month from a date
SELECT EXTRACT(MONTH FROM "2017-06-15");
Output:
6
This example extracts the month (June, which is 6) from the date 2017-06-15.
Example 2: Extract the week from a date
SELECT EXTRACT(WEEK FROM "2017-06-15");
Output:
24
This example extracts the week number (week 24) from the date 2017-06-15.
Example 3: Extract the minute from a datetime
SELECT EXTRACT(MINUTE FROM "2017-06-15 09:34:21");
Output:
34
This example extracts the minute (34) from the datetime 2017-06-15 09:34:21.
Example 4: Extract the year and month from a datetime
SELECT EXTRACT(YEAR_MONTH FROM "2017-06-15 09:34:21");
Output:
201706
This example extracts the year and month (2017-06) from the datetime 2017-06-15 09:34:21.
Use Cases
- Date Analysis: Useful when analyzing or reporting on specific parts of a date, such as extracting the month or year for monthly or yearly reports.
- Event Scheduling: Extract parts of a datetime to determine which month, day, or week a specific event falls on.
- Time Calculations: Extract specific time components (hour, minute, second) for calculations involving time differences.
Related Functions
YEAR(): Extracts the year from a date.MONTH(): Extracts the month from a date.DAY(): Extracts the day of the month from a date.DATE_FORMAT(): Formats a date based on specific patterns, including extracting parts like year, month, day, etc.