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

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+

Note

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