How to use the MySQL EXTRACT() function

The EXTRACT() function in MySQL retrieves a specific component from a date or datetime value. This can extract elements like day, month, year, etc.

Syntax

The syntax for EXTRACT() is:

EXTRACT(unit FROM date)

Where unit is the component to extract and date is the date/datetime value.

Examples

Some examples of using EXTRACT() in MySQL:

SELECT EXTRACT(DAY FROM '2023-11-14');

-- Returns 14

Extract the day part of the given date.

SELECT EXTRACT(MONTH FROM order_date)
FROM orders;

Get the month number for each order_date in the orders table.

SELECT name, EXTRACT(YEAR FROM birthday)
FROM users;

Retrieve the name and year of birth for each user.

SELECT EXTRACT(HOUR FROM creation_time) AS hour
FROM posts;

Extract just the hour part from the creation_time of posts.

SELECT EXTRACT(DAY_MICROSECOND FROM now());

-- Returns 1418000000

Extract day and microseconds to get detailed timestamp.

Other Date Functions

Some other useful MySQL date functions:

• DATE_FORMAT() - Format dates in queries
• DAY() - Day of month
• DAYOFWEEK() - Indexed day of week
• MONTH() - Numeric month from date
• YEAR() - Year from date/datetime

So EXTRACT() provides granular date part extraction.