Blog

MySQL: EXTRACT Function

  • Post author:
  • Post category:MySQL
  • Post comments:1 Comment
MySQL EXTRACT Function

In this guide, we will explain 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:

unitCompatibility
MICROSECOND4.1.1+
SECOND3.2.3+
MINUTE3.2.3+
HOUR3.2.3+
DAY3.2.3+
WEEK5+
MONTH3.2.3+
QUARTER5+
YEAR3.2.3+
SECOND_MICROSECOND4.1.1+
MINUTE_MICROSECOND4.1.1+
MINUTE_SECOND4.1.1+
HOUR_MICROSECOND4.1.1+
HOUR_SECOND4.1.1+
HOUR_MINUTE3.2.3+
DAY_MICROSECOND4.1.1+
DAY_SECOND3.2.3+
DAY_MINUTE3.2.3+
DAY_HOUR3.2.3+
YEAR_MONTH3.2.3+

Note

  • See also the YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, and MICROSECOND functions.

Applies To

The EXTRACT function can be used in the following versions :

  • 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 EXTRACT function examples and explore how to use the EXTRACT function.

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

Next Topic : Click Here

This Post Has One Comment

Leave a Reply