Mysql Extract Function
MySQL: EXTRACT Function
This tutorial explains how to use the MySQL EXTRACT function with syntax and examples.
Description
The MySQL EXTRACT function extracts parts from a date.
Syntax
The syntax for the MySQL EXTRACT function is:
EXTRACT( unit FROM date )Parameters or Arguments
- date
- unit
The date or datetime value from which the date part is to be extracted.
The unit type of the interval such as DAY, MONTH, MINUTE, HOUR, and so on. It can be one of the following:
| unit | Compatibility |
|---|---|
| MICROSECOND | 4.1.1+ |
| SECOND | 3.2.3+ |
| MINUTE | 3.2.3+ |
| HOUR | 3.2.3+ |
| DAY | 3.2.3+ |
| WEEK | 5+ |
| MONTH | 3.2.3+ |
| QUARTER | 5+ |
| YEAR | 3.2.3+ |
| SECOND_MICROSECOND | 4.1.1+ |
| MINUTE_MICROSECOND | 4.1.1+ |
| MINUTE_SECOND | 4.1.1+ |
| HOUR_MICROSECOND | 4.1.1+ |
| HOUR_SECOND | 4.1.1+ |
| HOUR_MINUTE | 3.2.3+ |
| DAY_MICROSECOND | 4.1.1+ |
| DAY_SECOND | 3.2.3+ |
| DAY_MINUTE | 3.2.3+ |
| DAY_HOUR | 3.2.3+ |
| YEAR_MONTH | 3.2.3+ |
Applies To
The EXTRACT function can be used in the following versions of MySQL:
- MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23
Example
Let's look at some MySQL EXTRACT function examples and explore how to use the EXTRACT function in MySQL.
For example:
mysql> SELECT EXTRACT(MICROSECOND FROM '2014-02-13 08:44:21.000001');
Output: 1
mysql> SELECT EXTRACT(SECOND FROM '2014-02-13 08:44:21');
Output: 21
mysql> SELECT EXTRACT(MINUTE FROM '2014-02-13 08:44:21');
Output: 44
mysql> SELECT EXTRACT(HOUR FROM '2014-02-13 08:44:21');
Output: 8
mysql> SELECT EXTRACT(DAY FROM '2014-02-13');
Output: 13
mysql> SELECT EXTRACT(WEEK FROM '2014-02-13');
Output: 6
mysql> SELECT EXTRACT(MONTH FROM '2014-02-13');
Output: 2
mysql> SELECT EXTRACT(QUARTER FROM '2014-02-13');
Output: 1
mysql> SELECT EXTRACT(YEAR FROM '2014-02-13');
Output: 2014
mysql> SELECT EXTRACT(SECOND_MICROSECOND FROM '2014-02-13 08:44:21.000001');
Output: 21000001
mysql> SELECT EXTRACT(MINUTE_MICROSECOND FROM '2014-02-13 08:44:21.000001');
Output: 4421000001
mysql> SELECT EXTRACT(MINUTE_SECOND FROM '2014-02-13 08:44:21');
Output: 4421
mysql> SELECT EXTRACT(HOUR_MICROSECOND FROM '2014-02-13 08:44:21.000001');
Output: 84421000001
mysql> SELECT EXTRACT(HOUR_SECOND FROM '2014-02-13 08:44:21');
Output: 84421
mysql> SELECT EXTRACT(HOUR_MINUTE FROM '2014-02-13 08:44:21');
Output: 844
mysql> SELECT EXTRACT(DAY_MICROSECOND FROM '2014-02-13 08:44:21.000001');
Output: 13084421000001
mysql> SELECT EXTRACT(DAY_SECOND FROM '2014-02-13 08:44:21');
Output: 13084421
mysql> SELECT EXTRACT(DAY_MINUTE FROM '2014-02-13 08:44:21');
Output: 130844
mysql> SELECT EXTRACT(DAY_HOUR FROM '2014-02-13 08:44:21');
Output: 1308
mysql> SELECT EXTRACT(YEAR_MONTH FROM '2014-02-13');
Output: 201402