EXTRACT() Function in MySQL

The EXTRACT() function in MySQL is a versatile tool used for retrieving specific components of date Whether we need the year, month, day or even the hour or minute.

This function simplifies date manipulation and makes queries involving date and time data more efficient and easier to understand. In this article, We will learn about the EXTRACT() Function in MySQL by understanding various examples.

EXTRACT() Function in MySQL

• The EXTRACT() function in MySQL is used to retrieve a specific part of a date or time value from a given DATETIME, DATE or TIMESTAMP data type.
• This function allows us to extract parts such as year, month, day, hour, minute, second etc.

Syntax:

EXTRACT(unit FROM date)

Parameter:

This method accepts two parameters which are illustrated below:

• part – Specified part to extract like SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR, etc.
• date – Specified date to extract a part from.

Returns:

It returns the desired extracted part from a specified date.

Examples of EXTRACT() Function in MySQL

Example 1: Extracting the Year

To extract the year from a date:

SELECT EXTRACT(YEAR FROM '2024-09-10') AS Year;

Output:

2024

Example 2: Extracting the Month

To extract the month from a date:

SELECT EXTRACT(MONTH FROM '2024-09-10') AS Month;

Output:

9

Example 3: Extracting the Day

To extract the day from a date:

SELECT EXTRACT(DAY FROM '2024-09-10') AS Day;

Output:

10

Example 4: Extracting the Hour

If you have a DATETIME or TIMESTAMP value, you can extract the hour:

SELECT EXTRACT(HOUR FROM '2024-09-10 15:30:00') AS Hour;

Output:

15

Advantages of the EXTRACT() Function in MySQL

1. Simplifies Date/Time Manipulation:

• The EXTRACT() function provides an easy way to isolate specific parts of a date or time, such as year, month, day, hour, etc., without having to perform complex string manipulation or conversions.

2. Improves Query Readability:

• By using EXTRACT(), queries that deal with dates become more readable and maintainable.
• We can easily understand the intent when extracting specific parts of a date or time.

3. Efficient Data Filtering:

• EXTRACT() helps in efficiently filtering or grouping records based on parts of a date or time, such as retrieving records from a particular month, year, or day of the week.

4. Supports Various Date/Time Units:

• The function supports a wide range of units (e.g., year, quarter, week, day, hour), offering flexibility when dealing with different date and time-related requirements.

5. Works with Multiple Data Types

• EXTRACT() is compatible with DATETIME, DATE, and TIMESTAMP types, allowing it to be used in various scenarios where different types of date/time data are stored.

Conclusion

The EXTRACT() function in MySQL simplifies the process of retrieving specific parts of a date or time value, making it easier to work with date-related data in queries. It’s useful for operations like filtering, grouping, or formatting date values in reports and analyses.