SQL Tutorial SQL Advanced SQL Database SQL References

MySQL EXTRACT() Function



The MySQL EXTRACT() function is used to extract parts from a date or datetime value.

Syntax

EXTRACT(unit FROM datetime)

Parameters

datetime Required. Specify a date or datetime value from which the parts is to be extracted.
unit Required. Specify the unit type of interval to extract. It 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

Return Value

Returns the extracted part.

Example 1:

The example below shows the usage of EXTRACT() function.

mysql> SELECT EXTRACT(DAY FROM '2019-10-25');
Result: 25

mysql> SELECT EXTRACT(HOUR FROM '2019-10-25 08:10:25');
Result: 8

mysql> SELECT EXTRACT(MINUTE FROM '2019-10-25 08:10:25');
Result: 10

mysql> SELECT EXTRACT(SECOND FROM '2019-10-25 08:10:25');
Result: 25

mysql> SELECT EXTRACT(WEEK FROM '2019-10-25 08:10:25');
Result: 42

mysql> SELECT EXTRACT(MONTH FROM '2019-10-25');
Result: 10

mysql> SELECT EXTRACT(QUARTER FROM '2019-10-25');
Result: 4

mysql> SELECT EXTRACT(YEAR FROM '2019-10-25');
Result: 2019

mysql> SELECT EXTRACT(MICROSECOND FROM '2019-10-25 08:10:25.000123');
Result: 123

mysql> SELECT EXTRACT(SECOND_MICROSECOND FROM '2019-10-25 08:10:25.000123');
Result: 25000123

mysql> SELECT EXTRACT(MINUTE_MICROSECOND FROM '2019-10-25 08:10:25.000123');
Result: 1025000123

mysql> SELECT EXTRACT(MINUTE_SECOND FROM '2019-10-25 08:10:25');
Result: 1025

mysql> SELECT EXTRACT(HOUR_MICROSECOND FROM '2019-10-25 08:10:25.000123');
Result: 81025000123

mysql> SELECT EXTRACT(HOUR_SECOND FROM '2019-10-25 08:10:25');
Result: 81025

mysql> SELECT EXTRACT(HOUR_MINUTE FROM '2019-10-25 08:10:25');
Result: 810

mysql> SELECT EXTRACT(DAY_MICROSECOND FROM '2019-10-25 08:10:25.000123');
Result: 25081025000123

mysql> SELECT EXTRACT(DAY_SECOND FROM '2019-10-25 08:10:25');
Result: 25081025

mysql> SELECT EXTRACT(DAY_MINUTE FROM '2019-10-25 08:10:25');
Result: 250810

mysql> SELECT EXTRACT(DAY_HOUR FROM '2019-10-25 08:10:25');
Result: 2508

mysql> SELECT EXTRACT(YEAR_MONTH FROM '2019-10-25 08:10:25');
Result: 201910

Example 2:

Consider a database table called EmployeeLogin with the following records:

EmpIDNameLoginStamp
1John2019-10-25 09:20:38
2Marry2019-10-25 09:21:05
3Jo2019-10-25 09:24:35
4Kim2019-10-25 09:25:24
5Ramesh2019-10-25 09:27:16
6Suresh2019-10-25 09:28:19

To extract the HOUR_MINUTE part from the records of LoginStamp column, the following query can be used:

SELECT *, EXTRACT(HOUR_MINUTE FROM LoginStamp) AS EXTRACT_Value FROM EmployeeLogin;

This will produce a result similar to:

EmpIDNameLoginStampEXTRACT_Value
1John2019-10-25 09:20:38920
2Marry2019-10-25 09:21:05921
3Jo2019-10-25 09:24:35924
4Kim2019-10-25 09:25:24925
5Ramesh2019-10-25 09:27:16927
6Suresh2019-10-25 09:28:19928

❮ MySQL Functions