MySQL EXTRACT Function
Summary: in this tutorial, you will learn how to use the MySQL EXTRACT() function to extract part of a DATE or DATETIME value.
Introduction to the MySQL EXTRACT() function
The EXTRACT() function extracts part of a date. The following illustrates the syntax of the EXTRACT() function.
EXTRACT(unit FROM date)
Code language: SQL (Structured Query Language) (sql)
The EXTRACT() function requires two arguments unit and date.
The unit is the interval that you want to extract from the date. The following are the valid intervals for the unit argument.
- 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
The date is a DATE or DATETIME value from which you extract an interval.
MySQL EXTRACT function examples
Extract day from a datetime:
mysql> SELECT EXTRACT(DAY FROM '2017-07-14 09:04:44') DAY;
+------+
| DAY |
+------+
| 14 |
+------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Extract day_hour from a datetime:
mysql> SELECT EXTRACT(DAY_HOUR FROM '2017-07-14 09:04:44') DAYHOUR;
+---------+
| DAYHOUR |
+---------+
| 1409 |
+---------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Extract day_microsecond from a datetime:
mysql> SELECT EXTRACT(DAY_MICROSECOND FROM '2017-07-14 09:04:44') DAY_MS;
+----------------+
| DAY_MS |
+----------------+
| 14090444000000 |
+----------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Extract day_minute from a datetime:
mysql> SELECT EXTRACT(DAY_MINUTE FROM '2017-07-14 09:04:44') DAY_M;
+--------+
| DAY_M |
+--------+
| 140904 |
+--------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Extract day_second from a datetime
mysql> SELECT EXTRACT(DAY_SECOND FROM '2017-07-14 09:04:44') DAY_S;
+----------+
| DAY_S |
+----------+
| 14090444 |
+----------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Extract hour from a datetime:
mysql> SELECT EXTRACT(HOUR FROM '2017-07-14 09:04:44') HOUR;
+------+
| HOUR |
+------+
| 9 |
+------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Extract hour_microsecond from a datetime:
mysql> SELECT EXTRACT(HOUR_MICROSECOND FROM '2017-07-14 09:04:44') HOUR_MS;
+-------------+
| HOUR_MS |
+-------------+
| 90444000000 |
+-------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Extract hour_minute from a datetime:
mysql> SELECT EXTRACT(HOUR_MINUTE FROM '2017-07-14 09:04:44') HOUR_M;
+--------+
| HOUR_M |
+--------+
| 904 |
+--------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Extract hour_second from a datetime:
mysql> SELECT EXTRACT(HOUR_SECOND FROM '2017-07-14 09:04:44') HOUR_S;
+--------+
| HOUR_S |
+--------+
| 90444 |
+--------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Extract microsecond from a datetime:
mysql> SELECT EXTRACT(MICROSECOND FROM '2017-07-14 09:04:44') MICROSECOND;
+-------------+
| MICROSECOND |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Extract minute from a datetime:
mysql> SELECT EXTRACT(MINUTE FROM '2017-07-14 09:04:44') MINUTE;
+--------+
| MINUTE |
+--------+
| 4 |
+--------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Extract minute_microsecond from a datetime:
mysql> SELECT EXTRACT(MINUTE_MICROSECOND FROM '2017-07-14 09:04:44') MINUTE_MS;
+-----------+
| MINUTE_MS |
+-----------+
| 444000000 |
+-----------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Extract minute_second from a datetime:
mysql> SELECT EXTRACT(MINUTE_SECOND FROM '2017-07-14 09:04:44') MINUTE_S;
+----------+
| MINUTE_S |
+----------+
| 444 |
+----------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Extract month from a datetime:
mysql> SELECT EXTRACT(MONTH FROM '2017-07-14 09:04:44') MONTH;
+-------+
| MONTH |
+-------+
| 7 |
+-------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Extract quarter from a datetime:
mysql> SELECT EXTRACT(QUARTER FROM '2017-07-14 09:04:44') QUARTER;
+---------+
| QUARTER |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Extract second from a datetime:
mysql> SELECT EXTRACT(SECOND FROM '2017-07-14 09:04:44') SECOND;
+--------+
| SECOND |
+--------+
| 44 |
+--------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Extract second_microsecond from a datetime:
mysql> SELECT EXTRACT(SECOND_MICROSECOND FROM '2017-07-14 09:04:44') SECOND_MS;
+-----------+
| SECOND_MS |
+-----------+
| 44000000 |
+-----------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Extract week from a datetime:
mysql> SELECT EXTRACT(WEEK FROM '2017-07-14 09:04:44') WEEK;
+------+
| WEEK |
+------+
| 28 |
+------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Extract year from a datetime:
mysql> SELECT EXTRACT(YEAR FROM '2017-07-14 09:04:44') YEAR;
+------+
| YEAR |
+------+
| 2017 |
+------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Extract year_month from a datetime
mysql> SELECT EXTRACT(YEAR_MONTH FROM '2017-07-14 09:04:44') YEARMONTH;
+-----------+
| YEARMONTH |
+-----------+
| 201707 |
+-----------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to use the MySQL EXTRACT() function to extract part of a DATE or DATETIME value.