Home » Learn – MySQL Extract

Learn – MySQL Extract

  • by

Learn – MySQL Extract

EXTRACT() function in MySQL is related to a DATE and DATETIME function. It is used to extract a portion of the DATE and DATETIME values. For example, we can extract the year portion, the month portion, the day portion, minutes, seconds, microseconds, etc. from the DATE and DATETIME value specified in the function argument. MySQL provides support for the EXTRACT function from version 4.0 and later.
Syntax:

The following is the syntax that illustrates the use of the EXTRACT function in MySQL:

Syntax

EXTRACT(unit_value FROM date_value);

In the above, we can see that the EXTRACT() function takes two arguments, unit_value and date_value. Now, date_value represents the DATE and DATETIME value from which we want to extract the portion. Here unit_value represents the portion of the date that we want to extract.

EXTRACT(unit_value FROM date_value);

The unit_value argument can use the below valid intervals in MySQL like

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

MySQL EXTRACT Function Examples

Say for example we want to Extract Day from DATETIME

mysql > SELECT EXTRACT(DAY FROM '2020-07-15 08: 06: 44') AS DAY;

mysql > SELECT EXTRACT(DAY_HOUR FROM '2020-07-15 08: 06:44') AS DAY;

mysql > SELECT EXTRACT(DAY_MICROSECOND FROM '2020-07-15 08: 06: 44') AS DAY_MS;

mysql > SELECT EXTRACT(HOUR_MINUTE FROM '2020-07-15 08: 06: 44') AS Hrs_Min;