MySQL Extract

EXTRACT() function in MySQL is related to a DATE and DATETIME function. It is used to extract a portion of the DATE and DATETIME values. For example, we can extract the year portion, the month portion, the day portion, minutes, seconds, microseconds, etc. from the DATE and DATETIME value specified in the function argument. MySQL provides support for the EXTRACT function from version 4.0 and later.

Syntax:

The following is the syntax that illustrates the use of the EXTRACT function in MySQL:

In the above, we can see that the EXTRACT() function takes two arguments, unit_value and date_value. Here, date_value represents the DATE and DATETIME value from which we want to extract the portion. And unit_value represents the portion of the date that we want to extract.

The unit_value argument can use the below valid intervals in MySQL:

• DAY
• DAY_HOUR
• DAY_MICROSECOND
• DAY_MINUTE
• DAY_SECOND
• HOUR
• HOUR_MICROSECOND
• HOUR_MINUTE
• HOUR_SECOND
• MICROSECOND
• MINUTE
• MINUTE_MICROSECOND
• MINUTE_SECOND
• MONTH
• QUARTER
• SECOND
• SECOND_MICROSECOND
• WEEK
• YEAR
• YEAR_MONTH

MySQL EXTRACT Function Example

Let us learn how to use the EXTRACT function in MySQL through various examples.

Extract Day from DATETIME

Execute the below statement to extract date from DATETIME:

Output:

Extract DAY_HOUR from DATETIME

Execute the below statement to extract date and hour from DATETIME:

Output:

Extract DAY_MICROSECOND from DATETIME

Execute the below statement to extract date and microsecond from DATETIME:

Output:

Extract DAY_MINUTE from DATETIME

Execute the below statement to extract date and minute from DATETIME:

Output:

Extract DAY_SECOND from DATETIME

Execute the below statement to extract date and second from DATETIME:

Output:

Extract HOUR from DATETIME

Execute the below statement to extract hour from DATETIME:

Output:

Extract HOUR_MICROSECOND from DATETIME

Execute the below statement to extract hour and microsecond from DATETIME:

Output:

Extract HOUR_MINUTE from DATETIME

Execute the below statement to extract hour and minute from DATETIME:

Output:

Extract HOUR_SECOND from DATETIME

Execute the below statement to extract hour and second from DATETIME:

Output:

Extract MICROSECOND from DATETIME

Execute the below statement to extract microsecond from DATETIME:

Output:

Extract MINUTE from DATETIME

Execute the below statement to extract minute from DATETIME:

Output:

Extract SECOND from DATETIME

Execute the below statement to extract second from DATETIME:

Output:

Extract MONTH from DATETIME

Execute the below statement to extract month from DATETIME:

Output:

Extract YEAR from DATETIME

Execute the below statement to extract year from DATETIME:

Output:

Extract WEEK from DATETIME

Execute the below statement to extract week from DATETIME:

Output:

Extract QUARTER from DATETIME

Execute the below statement to extract quarter from DATETIME:

Output: