MySQL: EXTRACT Function
This MySQL 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
- The date or datetime value from which the date part is to be extracted.
- unit
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'); Result: 1 mysql> SELECT EXTRACT(SECOND FROM '2014-02-13 08:44:21'); Result: 21 mysql> SELECT EXTRACT(MINUTE FROM '2014-02-13 08:44:21'); Result: 44 mysql> SELECT EXTRACT(HOUR FROM '2014-02-13 08:44:21'); Result: 8 mysql> SELECT EXTRACT(DAY FROM '2014-02-13'); Result: 13 mysql> SELECT EXTRACT(WEEK FROM '2014-02-13'); Result: 6 mysql> SELECT EXTRACT(MONTH FROM '2014-02-13'); Result: 2 mysql> SELECT EXTRACT(QUARTER FROM '2014-02-13'); Result: 1 mysql> SELECT EXTRACT(YEAR FROM '2014-02-13'); Result: 2014 mysql> SELECT EXTRACT(SECOND_MICROSECOND FROM '2014-02-13 08:44:21.000001'); Result: 21000001 mysql> SELECT EXTRACT(MINUTE_MICROSECOND FROM '2014-02-13 08:44:21.000001'); Result: 4421000001 mysql> SELECT EXTRACT(MINUTE_SECOND FROM '2014-02-13 08:44:21'); Result: 4421 mysql> SELECT EXTRACT(HOUR_MICROSECOND FROM '2014-02-13 08:44:21.000001'); Result: 84421000001 mysql> SELECT EXTRACT(HOUR_SECOND FROM '2014-02-13 08:44:21'); Result: 84421 mysql> SELECT EXTRACT(HOUR_MINUTE FROM '2014-02-13 08:44:21'); Result: 844 mysql> SELECT EXTRACT(DAY_MICROSECOND FROM '2014-02-13 08:44:21.000001'); Result: 13084421000001 mysql> SELECT EXTRACT(DAY_SECOND FROM '2014-02-13 08:44:21'); Result: 13084421 mysql> SELECT EXTRACT(DAY_MINUTE FROM '2014-02-13 08:44:21'); Result: 130844 mysql> SELECT EXTRACT(DAY_HOUR FROM '2014-02-13 08:44:21'); Result: 1308 mysql> SELECT EXTRACT(YEAR_MONTH FROM '2014-02-13'); Result: 201402
Advertisements