The MySQL EXTRACT function is a powerful tool that allows users to extract specific parts of a date or time value. It is particularly useful when you want to retrieve information such as the year, month, day, hour, minute, or second from a given date or time expression.

Syntax

The syntax for the EXTRACT function is as follows:

EXTRACT(unit FROM datetime_expression)

Here, “unit” specifies the part of the date or time you want to extract, and “datetime_expression” is the date or time value from which you want to extract the information.

Examples

Let’s explore the various units that can be used with the EXTRACT function:

YEAR

SELECT EXTRACT(YEAR FROM '2024-01-01');

This query would return 2024.

MONTH

SELECT EXTRACT(MONTH FROM '2024-01-01');

This query would return 1.

DAY

SELECT EXTRACT(DAY FROM '2024-01-01');

This query would return 1.

HOUR

SELECT EXTRACT(HOUR FROM '2024-01-01 12:30:45');

This query would return 12.

MINUTE

SELECT EXTRACT(MINUTE FROM '2024-01-01 12:30:45');

This query would return 30.

SECOND

SELECT EXTRACT(SECOND FROM '2024-01-01 12:30:45');

This query would return 45.

The EXTRACT function is handy when you need to perform operations based on specific components of a date or time. For example, you might want to retrieve all records from a database where the year is 2024 or find the average number of orders placed per month. In such cases, the EXTRACT function simplifies the process by allowing you to focus on the relevant units of time.

It’s important to note that the datetime_expression should be a valid date or time value, and the unit should be specified in uppercase. The EXTRACT function adds flexibility and precision to date and time manipulations in MySQL queries.