MySQL - EXTRACT() Function

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

The MYSQL EXTRACT() function is used to retrieve and return the part of the given date or date time expression, specified by unit.

Syntax

Following is the syntax of the above function –

EXTRACT(unit FROM expr);

Where,

unit is the interval type represented by the expr value which can be DAY, WEEK, MONTH, QUARTER, YEAR, HOUR, MINUTE, SECOND, MICROSECOND.

The unit can be mixed values as: SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH.

expr is the date-time or the time expression from which you need to extract the specified part.

Example 1

Following example demonstrates the usage of the EXTRACT() function –

mysql> SELECT EXTRACT(YEAR FROM '2019-05-25');
+---------------------------------+
| EXTRACT(YEAR FROM '2019-05-25') |
+---------------------------------+
|                            2019 |
+---------------------------------+
1 row in set (0.00 sec)

Example 2

Following is another example of this function –

mysql> SELECT EXTRACT(DAY FROM '1998-11-21');
+--------------------------------+
| EXTRACT(DAY FROM '1998-11-21') |
+--------------------------------+
|                             21 |
+--------------------------------+
1 row in set (0.00 sec)
mysql> SELECT EXTRACT(MONTH FROM '1998-11-21');
+----------------------------------+
| EXTRACT(MONTH FROM '1998-11-21') |
+----------------------------------+
|                               11 |
+----------------------------------+
1 row in set (0.00 sec)

Example 3

We can also pass the date-time expression as an argument to this function –

mysql> SELECT EXTRACT(MINUTE FROM '2015-09-05 09:40:45.2300');
+-------------------------------------------------+
| EXTRACT(MINUTE FROM '2015-09-05 09:40:45.2300') |
+-------------------------------------------------+
|                                              40 |
+-------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT EXTRACT(HOUR FROM '2015-09-05 09:40:45.2300');
+-----------------------------------------------+
| EXTRACT(HOUR FROM '2015-09-05 09:40:45.2300') |
+-----------------------------------------------+
|                                             9 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT EXTRACT(SECOND FROM '2015-09-05 09:40:45.2300');
+-------------------------------------------------+
| EXTRACT(SECOND FROM '2015-09-05 09:40:45.2300') |
+-------------------------------------------------+
|                                              45 |
+-------------------------------------------------+
1 row in set (0.00 sec)

Example 4

In the following example we are retrieving the various values from the current date —

mysql> SELECT EXTRACT(YEAR FROM CURDATE());
+------------------------------+
| EXTRACT(YEAR FROM CURDATE()) |
+------------------------------+
|                         2021 |
+------------------------------+
1 row in set (0.00 sec)
mysql> SELECT EXTRACT(MONTH FROM CURDATE());
+-------------------------------+
| EXTRACT(MONTH FROM CURDATE()) |
+-------------------------------+
|                             7 |
+-------------------------------+
1 row in set (0.00 sec)
mysql> SELECT EXTRACT(DAY FROM CURDATE());
+-----------------------------+
| EXTRACT(DAY FROM CURDATE()) |
+-----------------------------+
|                          12 |
+-----------------------------+
1 row in set (0.00 sec)

Example 5

In the following example we are retrieving the various values from the current time —

mysql> SELECT EXTRACT(HOUR FROM CURTIME());
+------------------------------+
| EXTRACT(HOUR FROM CURTIME()) |
+------------------------------+
|                           19 |
+------------------------------+
1 row in set (0.00 sec)
mysql> SELECT EXTRACT(MINUTE FROM CURTIME());
+--------------------------------+
| EXTRACT(MINUTE FROM CURTIME()) |
+--------------------------------+
|                             20 |
+--------------------------------+
1 row in set (0.00 sec)
mysql> SELECT EXTRACT(SECOND FROM CURTIME());
+--------------------------------+
| EXTRACT(SECOND FROM CURTIME()) |
+--------------------------------+
|                             49 |
+--------------------------------+
1 row in set (0.00 sec)

Example 6

Following example demonstrates the usage of the units that represent more than one value –

mysql> SELECT EXTRACT(HOUR_MINUTE FROM '2018-05-23 20:40:32.88558');
+-------------------------------------------------------+
| EXTRACT(HOUR_MINUTE FROM '2018-05-23 20:40:32.88558') |
+-------------------------------------------------------+
|                                                  2040 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT EXTRACT(YEAR_MONTH FROM '2018-05-23 20:40:32.88558');
+------------------------------------------------------+
| EXTRACT(YEAR_MONTH FROM '2018-05-23 20:40:32.88558') |
+------------------------------------------------------+
|                                               201805 |
+------------------------------------------------------+
1 row in set (0.00 sec)

Example 7

You can also pass the column name as an argument to this function. Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below –

mysql> CREATE TABLE MyPlayers(
	ID INT,
	First_Name VARCHAR(255),
	Last_Name VARCHAR(255),
	Date_Of_Birth date,
	Place_Of_Birth VARCHAR(255),
	Country VARCHAR(255),
	PRIMARY KEY (ID)
);
Now, we will insert 7 records in MyPlayers table using INSERT statements:
mysql> insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
mysql> insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
mysql> insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following query extracts the year from all the entities of the Date_Of_Birth column of the table MyPlayers —

ysql> SELECT First_Name, Last_Name, Date_Of_Birth, Country,
EXTRACT(YEAR FROM Date_Of_Birth) as YEAR FROM MyPlayers;
+------------+------------+---------------+-------------+------+
| First_Name |  Last_Name | Date_Of_Birth | Country     | YEAR |
+------------+------------+---------------+-------------+------+
| Shikhar    | Dhawan     | 1981-12-05    | India       | 1981 |
| Jonathan   | Trott      | 1981-04-22    | SouthAfrica | 1981 |
| Kumara     | Sangakkara | 1977-10-27    | Srilanka    | 1977 |
| Virat      | Kohli      | 1988-11-05    | India       | 1988 |
| Rohit      | Sharma     | 1987-04-30    | India       | 1987 |
| Ravindra   | Jadeja     | 1988-12-06    | India       | 1988 |
| James      | Anderson   | 1982-06-30    | England     | 1982 |
+------------+------------+---------------+-------------+------+
7 rows in set (0.07 sec)

Example 8

Suppose we have created a table named dispatches_data with 5 records in it using the following queries –

mysql> CREATE TABLE dispatches_data(
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchTimeStamp timestamp,
	Price INT,
	Location VARCHAR(255)
);
insert into dispatches_data values('Key-Board', 'Raja', TIMESTAMP('2019-05-04', '15:02:45'), 7000, 'Hyderabad');
insert into dispatches_data values('Earphones', 'Roja', TIMESTAMP('2019-06-26', '14:13:12'), 2000, 'Vishakhapatnam');
insert into dispatches_data values('Mouse', 'Puja', TIMESTAMP('2019-12-07', '07:50:37'), 3000, 'Vijayawada');
insert into dispatches_data values('Mobile', 'Vanaja' , TIMESTAMP ('2018-03-21', '16:00:45'), 9000, 'Chennai');
insert into dispatches_data values('Headset', 'Jalaja' , TIMESTAMP('2018-12-30', '10:49:27'), 6000, 'Goa');

Following query retrieves the minute, hours and seconds values from the DispatchTimeStamp column —

mysql> SELECT ProductName, CustomerName, DispatchTimeStamp, Price,
EXTRACT(MINUTE FROM DispatchTimeStamp) as Minutes,
EXTRACT(HOUR FROM DispatchTimeStamp) as Hours,
EXTRACT(SECOND FROM DispatchTimeStamp) as Seconds FROM dispatches_data;
+-------------+--------------+---------------------+-------+---------+-------+---------+
| ProductName | CustomerName | DispatchTimeStamp   | Price | Minutes | Hours | Seconds |
+-------------+--------------+---------------------+-------+---------+-------+---------+
| Key-Board   | Raja         | 2019-05-04 15:02:45 | 7000  | 2       |     15|      45 |
| Earphones   | Roja         | 2019-06-26 14:13:12 | 2000  | 13      |     14|      12 |
| Mouse       | Puja         | 2019-12-07 07:50:37 | 3000  | 50      |     7 |      37 |
| Mobile      | Vanaja       | 2018-03-21 16:00:45 | 9000  | 0       |    16 |      45 |
| Headset     | Jalaja       | 2018-12-30 10:49:27 | 6000  | 49      |    10 |      27 |
+-------------+--------------+---------------------+-------+---------+-------+---------+
5 rows in set (0.00 sec)

Example 9

Suppose we have created a table named SubscriberDetails with 5 records in it using the following queries –

mysql> CREATE TABLE SubscriberDetails (
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	SubscriptionTimeStamp timestamp
);
insert into SubscriberDetails values('Raja', 'Premium', TimeStamp('2020-10-21 20:53:49'));
insert into SubscriberDetails values('Roja', 'Basic', TimeStamp('2020-11-26 10:13:19'));
insert into SubscriberDetails values('Puja', 'Moderate', TimeStamp('2021-03-07 05:43:20'));
insert into SubscriberDetails values('Vanaja', 'Basic', TimeStamp('2021-02-21 16:36:39'));
insert into SubscriberDetails values('Jalaja', 'Premium', TimeStamp('2021-01-30 12:45:45'));

Following query retrieves and displays the subscription year, month and day for all the records —

mysql> SELECT SubscriberName, PackageName, SubscriptionTimeStamp,
EXTRACT(YEAR FROM SubscriptionTimeStamp) as YEAR,
EXTRACT(MONTH FROM SubscriptionTimeStamp) as MONTH,
EXTRACT(DAY FROM SubscriptionTimeStamp) as DAY FROM SubscriberDetails;
+----------------+-------------+-----------------------+------+-------+------+
| SubscriberName | PackageName | SubscriptionTimeStamp | YEAR | MONTH |  DAY |
+----------------+-------------+-----------------------+------+-------+------+
| Ram            | Premium     | 2020-10-21 20:53:49   | 2020 | 10    | 21   |
| Rahman         | Basic       | 2020-11-26 10:13:19   | 2020 | 11    | 26   |
| Robert         | Moderate    | 2021-03-07 05:43:20   | 2021 | 3     | 7    |
| Radha          | Basic       | 2021-02-21 16:36:39   | 2021 | 2     | 21   |
| Rajiya         | Premium     | 2021-01-30 12:45:45   | 2021 | 1     | 30   |
+----------------+-------------+-----------------------+------+-------+------+
5 rows in set (0.00 sec)

mysql-date-time-functions.htm

Kickstart Your Career

Get certified by completing the course

Get Started