In MariaDB, EXTRACT() is a built-in date and time function that returns the specified unit from a given date or datetime expression.
Syntax
The syntax goes like this:
EXTRACT(unit FROM date)Where unit is a valid date and time unit, and date is the date to extract that unit from.
Example
Here’s an example that extracts the year from a date:
SELECT EXTRACT(YEAR FROM '2030-12-25');Result:
+---------------------------------+ | EXTRACT(YEAR FROM '2030-12-25') | +---------------------------------+ | 2030 | +---------------------------------+
Here’s another one that extracts the day:
SELECT EXTRACT(DAY FROM '2030-12-25');Result:
+--------------------------------+ | EXTRACT(DAY FROM '2030-12-25') | +--------------------------------+ | 25 | +--------------------------------+
Datetime Values
It also works with datetime values:
SELECT EXTRACT(HOUR FROM '2030-02-01 10:30:45');Result:
+------------------------------------------+ | EXTRACT(HOUR FROM '2030-02-01 10:30:45') | +------------------------------------------+ | 10 | +------------------------------------------+
Date & Time Units
Here’s another example that runs through each unit in the datetime expression:
SELECT
EXTRACT(YEAR FROM '2030-02-01 10:30:45.123456') AS YEAR,
EXTRACT(MONTH FROM '2030-02-01 10:30:45.123456') AS MONTH,
EXTRACT(DAY FROM '2030-02-01 10:30:45.123456') AS DAY,
EXTRACT(HOUR FROM '2030-02-01 10:30:45.123456') AS HOUR,
EXTRACT(MINUTE FROM '2030-02-01 10:30:45.123456') AS MINUTE,
EXTRACT(SECOND FROM '2030-02-01 10:30:45.123456') AS SECOND,
EXTRACT(MICROSECOND FROM '2030-02-01 10:30:45.123456') AS MICROSECOND;Result:
+------+-------+------+------+--------+--------+-------------+ | YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MICROSECOND | +------+-------+------+------+--------+--------+-------------+ | 2030 | 2 | 1 | 10 | 30 | 45 | 123456 | +------+-------+------+------+--------+--------+-------------+
Composite Units
Here’s an example that uses a composite unit. Composite units consist of multiple base time units.
SELECT EXTRACT(YEAR_MONTH FROM '2030-02-01');Result:
+---------------------------------------+ | EXTRACT(YEAR_MONTH FROM '2030-02-01') | +---------------------------------------+ | 203002 | +---------------------------------------+
Zero Units
Zero units result in 0.
Example:
SELECT
EXTRACT(YEAR FROM '0000-00-00 00:00:00.000000') AS YEAR,
EXTRACT(MONTH FROM '0000-00-00 00:00:00.000000') AS MONTH,
EXTRACT(DAY FROM '0000-00-00 00:00:00.000000') AS DAY,
EXTRACT(HOUR FROM '0000-00-00 00:00:00.000000') AS HOUR,
EXTRACT(MINUTE FROM '0000-00-00 00:00:00.000000') AS MINUTE,
EXTRACT(SECOND FROM '0000-00-00 00:00:00.000000') AS SECOND,
EXTRACT(MICROSECOND FROM '0000-00-00 00:00:00.000000') AS MICROSECOND;Result:
+------+-------+------+------+--------+--------+-------------+ | YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MICROSECOND | +------+-------+------+------+--------+--------+-------------+ | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +------+-------+------+------+--------+--------+-------------+
Numeric Dates
It’s also possible to pass dates as a number, as long as it makes sense as a date.
Example
SELECT EXTRACT(MONTH FROM 20301125);Result:
+------------------------------+ | EXTRACT(MONTH FROM 20301125) | +------------------------------+ | 11 | +------------------------------+
Or even the following (which uses a two-digit year):
SELECT EXTRACT(YEAR FROM 301125);Result:
+---------------------------+ | EXTRACT(YEAR FROM 301125) | +---------------------------+ | 2030 | +---------------------------+
But be careful here – MariaDB basically has to guess which year it is. Here’s what happens if I increase the year from 30 to 80:
SELECT EXTRACT(YEAR FROM 801125);Result:
+---------------------------+ | EXTRACT(YEAR FROM 801125) | +---------------------------+ | 1980 | +---------------------------+
So in this case, passing 30 resulted in 2030 but passing 80 returned 1980.
Also, it must make sense as a date. Here’s what happens if I use an invalid day:
SELECT EXTRACT(YEAR FROM 20300135);Result:
+-----------------------------+ | EXTRACT(YEAR FROM 20300135) | +-----------------------------+ | NULL | +-----------------------------+
Other Delimiters
You can use other delimiters for the date. MariaDB is quite forgiving when it comes to delimiters on dates. Here are some valid examples:
SELECT
EXTRACT(MONTH FROM '2030/06/25'),
EXTRACT(MONTH FROM '2030,06,25'),
EXTRACT(MONTH FROM '2030:06:25'),
EXTRACT(MONTH FROM '2030;06!25');Result (using vertical output):
EXTRACT(MONTH FROM '2030/06/25'): 6 EXTRACT(MONTH FROM '2030,06,25'): 6 EXTRACT(MONTH FROM '2030:06:25'): 6 EXTRACT(MONTH FROM '2030;06!25'): 6
Current Date
We can pass NOW() as the date argument to use the current date:
SELECT
NOW(),
EXTRACT(MONTH FROM NOW());Result:
+---------------------+---------------------------+ | NOW() | EXTRACT(MONTH FROM NOW()) | +---------------------+---------------------------+ | 2021-05-16 10:06:21 | 5 | +---------------------+---------------------------+
Invalid Dates
When passed an invalid date, EXTRACT() returns null:
SELECT EXTRACT(YEAR FROM 'Friday');Result:
+-----------------------------+ | EXTRACT(YEAR FROM 'Friday') | +-----------------------------+ | NULL | +-----------------------------+
Invalid Date/Time Unit
When passed an invalid date/time unit, EXTRACT() returns an error:
SELECT EXTRACT(DECADE FROM '2030-06-25');Result:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECADE FROM '2030-06-25')' at line 1
Missing Argument
Calling EXTRACT() without passing any arguments results in an error:
SELECT EXTRACT();Result:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1