MySQL EXTRACT Function

MySQL EXTRACT() Function

The EXTRACT() function in MySQL is a powerful tool for retrieving specific parts (like the year, month, day, hour, etc.) from a date or datetime value. It's particularly useful when you want to focus on a specific date component for querying, filtering, or reporting purposes.

Syntax

EXTRACT(unit FROM date)

unit: The part of the date you want to extract.
date: A valid DATE, DATETIME, or TIMESTAMP value.

Supported Units

Here’s a table of the supported units and their descriptions:

Unit	Description
`YEAR`	Extracts the year (e.g., 2025).
`QUARTER`	Extracts the quarter (1 to 4).
`MONTH`	Extracts the month (1 to 12).
`DAY`	Extracts the day of the month (1 to 31).
`WEEK`	Extracts the week number (1 to 53).
`DAYOFYEAR`	Extracts the day of the year (1 to 366).
`DAYOFMONTH`	Same as `DAY`.
`DAYOFWEEK`	Extracts the day of the week (1 = Sunday).
`HOUR`	Extracts the hour (0 to 23).
`MINUTE`	Extracts the minute (0 to 59).
`SECOND`	Extracts the second (0 to 59).
`MICROSECOND`	Extracts the microseconds.

Examples

Here are some examples of how to use the EXTRACT() function:

1. Extracting the Year

SELECT EXTRACT(YEAR FROM '2025-01-12') AS Year;
-- Output: 2025

2. Extracting the Month

SELECT EXTRACT(MONTH FROM '2025-01-12') AS Month;
-- Output: 1

3. Extracting the Day of the Week

SELECT EXTRACT(DAYOFWEEK FROM '2025-01-12') AS DayOfWeek;
-- Output: 1 (Sunday)

4. Extracting Time Components

SELECT 
    EXTRACT(HOUR FROM '2025-01-12 14:35:10') AS Hour,
    EXTRACT(MINUTE FROM '2025-01-12 14:35:10') AS Minute,
    EXTRACT(SECOND FROM '2025-01-12 14:35:10') AS Second;
-- Output: Hour = 14, Minute = 35, Second = 10

Use Cases

Filtering Records by Year or Month

SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2025;

Grouping Records by Quarter

SELECT EXTRACT(QUARTER FROM sale_date) AS Quarter, SUM(amount) AS Total
FROM sales
GROUP BY Quarter;

Analyzing Time-Specific Data

SELECT EXTRACT(HOUR FROM login_time) AS Hour, COUNT(*) AS Logins
FROM user_logins
GROUP BY Hour;

Notes and Best Practices

Performance: When used in WHERE or GROUP BY clauses, EXTRACT() may prevent the use of indexes, so it’s advisable to optimize queries by using indexed columns when possible.
Invalid Date Handling: If the date provided is invalid, MySQL will return NULL.

Conclusion

The EXTRACT() function is an essential tool in MySQL for working with date and time values. It enables developers to analyze and manipulate date components efficiently. Whether you're filtering by year, grouping by quarter, or analyzing hourly trends, EXTRACT() simplifies the process significantly.

Feel free to use these examples and tailor them to your specific needs!

Let me know if you'd like to add or modify anything!

Souy Soeng

MySQL EXTRACT Function