MySQL EXTRACT() Function

MySQL EXTRACT() Function Syntax

The MySQL EXTRACT() function follows this syntax:

In this syntax, there are two arguments:

1. date_value: This parameter represents the DATE or DATETIME value from which we want to extract a portion.
2. part: This document shows the specific part of the date that we want to retrieve or extract.

MySQL EXTRACT() Function Paramerters

The MySQL EXTRACT() function accepts two parameters:

1. part: This parameter specifies the part of the date to extract, such as SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR, and more.
2. date_value: This parameter shows the date from which a part should be extracted.

MySQL EXTRACT() Function Return Value

The MySQL EXTRACT() function is supposed to retrieve specific components from a given date, providing you with a precise result. When you use this function, it returns the extracted part from the specified date.

This function not only retrieves specific components from a date but also provides you with the flexibility to manipulate date and time data effectively.

MySQL EXTRACT() Function Examples

Extract the Week From a Date

Let's explore how to use the MySQL EXTRACT() function to extract the week from a given date:

Output:

This query extracts the week component from the specified date 2023-09-09, which corresponds to the $37^{th}$ week of the year 2023.

Sometimes, you might have date values with time information. In this case, you can still use the EXTRACT() function to extract the week.

Output:

Here also, the query extracts the week component from the specified date and time 2023-09-09 14:30:00, which corresponds to the $37^{th}$ week of the year 2023. The time portion is ignored when extracting the week.

Extract the Minute From a Datetime

You can use the following SQL query to extract the minute component from a datetime value:

Output:

This query extracts the minute portion from the specified datetime 2023-09-09 09:34:21, which is 34 minutes past the hour.

Extract HOUR From DATETIME

Here is the example to extract the hour from a DATETIME value:

Output:

This query extracts the hour component (8) from the given DATETIME 2023-09-09 08:02:24.

Extract the Year and Month From a Datetime

Let's now see an example of retrieving the year and month from the provided date and time:

Output:

The output 202309 represents the combined year and month extracted from the datetime 2023-09-09 09:09:09 using the EXTRACT function with the YEAR_MONTH unit in MySQL.

Extract MICROSECOND From DATETIME

Let's see an example to extract the microsecond from a DATETIME:

Output:

In this query, the EXTRACT function is used to extract the microsecond part from the DATETIME value 2023-09-09 09:04:23. Since the DATETIME value in the example does not include a microsecond component (it's 0 microseconds), the extracted microsecond is 0.

Let's see another example to extract the microsecond from a DATETIME:

Output:

In this query, the EXTRACT function is used to extract the microsecond part from the DATETIME value 2023-09-09 09:04:23.456789. The extracted microsecond is 456789, representing the microseconds portion of the datetime value.

Extract HOUR_MICROSECOND From DATETIME

Let's see an example to extract the hour and microsecond components from a DATETIME value:

Output:

In this query, the EXTRACT function is used with the HOUR_MICROSECOND unit to extract both the hour and microsecond parts from the DATETIME value 2023-09-09 02:16:21. The result 21621000 represents the hour as 2 and the microsecond part as 1621000.

Extract DAY_MICROSECOND From DATETIME

Consider an example below to extract the date and microsecond components from a DATETIME value:

Output:

In this query, the EXTRACT function is used with the DAY_MICROSECOND unit to extract both the date and microsecond parts from the DATETIME value 2023-09-09 02:16:21. The result 92016000 represents the date as 9 (the $9^{th}$ day of September) and the microsecond part as 2016000.