MySQL EXTRACT Function

 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() the function extracts part of a date. The following illustrates the syntax of the EXTRACT() function.

EXTRACT(unit FROM date)

The EXTRACT() the 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)

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)

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)

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)

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)

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)

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)

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)

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)

Extract a 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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

In this tutorial, you have learned how to use the MySQL EXTRACT() function to extract part of a DATE or DATETIME value.