MariaDB EXTRACT() Function

In MariaDB, EXTRACT() is a built-in function that extracts specified parts from a given date or datetime expression.

MariaDB `EXTRACT()` Syntax

This is the syntax of the MariaDB EXTRACT() function:

EXTRACT(unit FROM date)

Parameters

unit

Required. The unit to extract. unit can be one of the following values:

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

You can view all MariaDB date and time units here.

date

Required. A date or datetime expression.

If you provide no parameters or the wrong number of parameters, MariaDB will report an error: 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.

Return value

The MariaDB EXTRACT() function extracts the specified part from the specified date/time and returns it.

If the specified expression is not a valid date or datetime, the EXTRACT() function will return NULL.

If the argument is NULL, the EXTRACT() function will return NULL.

MariaDB `EXTRACT()` Examples

This statement shows the basic usage of the MariaDB EXTRACT() function:

SELECT
    EXTRACT(YEAR FROM '2022-02-28'),
    EXTRACT(MONTH FROM '2022-02-28'),
    EXTRACT(DAY FROM '2022-02-28'),
    EXTRACT(HOUR FROM '10:11:12'),
    EXTRACT(MINUTE FROM '10:11:12'),
    EXTRACT(SECOND FROM '10:11:12'),
    EXTRACT(HOUR FROM '2022-02-28 10:11:12'),
    EXTRACT(MINUTE FROM '2022-02-28 10:11:12'),
    EXTRACT(SECOND FROM '2022-02-28 10:11:12')\G

Output:

           EXTRACT(YEAR FROM '2022-02-28'): 2022
          EXTRACT(MONTH FROM '2022-02-28'): 2
            EXTRACT(DAY FROM '2022-02-28'): 28
             EXTRACT(HOUR FROM '10:11:12'): 10
           EXTRACT(MINUTE FROM '10:11:12'): 11
           EXTRACT(SECOND FROM '10:11:12'): 12
  EXTRACT(HOUR FROM '2022-02-28 10:11:12'): 10
EXTRACT(MINUTE FROM '2022-02-28 10:11:12'): 11
EXTRACT(SECOND FROM '2022-02-28 10:11:12'): 12

Current date

You can use MariaDB EXTRACT() function to extract any part from the current date:

SELECT
  NOW(),
  EXTRACT(YEAR FROM NOW()) "YEAR",
  EXTRACT(MONTH FROM NOW()) "MONTH",
  EXTRACT(DAY FROM NOW()) "DAY",
  EXTRACT(HOUR FROM NOW()) "HOUR",
  EXTRACT(MINUTE FROM NOW()) "MINUTE",
  EXTRACT(SECOND FROM NOW()) "MINUTE";

Output:

+---------------------+------+-------+------+------+--------+--------+
| NOW()               | YEAR | MONTH | DAY  | HOUR | MINUTE | MINUTE |
+---------------------+------+-------+------+------+--------+--------+
| 2023-01-08 15:41:29 | 2023 |     1 |    8 |   15 |     41 |     29 |
+---------------------+------+-------+------+------+--------+--------+

Conclusion