MySQL EXTRACT Function | Extract Part Of A Date

In this post, we discuss MySQL EXTRACT function. Many times while writing queries we are required to fetch a certain part of date or timestamp. We can do this very easily by making use of EXTRACT() function, which is an inbuilt function of MySQL. In this article, we will discuss the EXTRACT Function.

What Is MySQL EXTRACT Function?

EXTRACT() function extracts the specified “Part” from the “Date” and gives the value of the given Part as output.

Syntax

EXTRACT(Part FROM Date);

Extract Expects 2 parameters :

  • Part: Interval which is to be extracted from the given date.
  • Date: Date from which interval is to be extracted.

Part is the value that is extracted from Date and it can have following valid intervals:

  • YEAR
  • QUARTER
  • MONTH
  • WEEK
  • DAY
  • HOUR
  • MINUTE
  • SECONDS
  • MICROSECONDS
  • YEAR_MONTH
  • DAY_HOUR
  • DAY_MINUTE
  • DAY_SECOND
  • DAY_MICROSECOND
  • HOUR_MINUTE
  • HOUR_SECOND
  • HOUR_MICROSECOND
  • MINUTE_SECOND
  • MINUTE_MICROSECOND
  • SECOND_MICROSECOND

Let us now go through some examples as of how to use extract() function. We are using the users table. It contains id and name of the users, the date on which users are added and timestamp of the corresponding date. Create and Insert statements for the table are given below.

CREATE TABLE users(
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
date_added_on datetime NOT NULL,
timestamp_added_on int(11) NOT NULL,
PRIMARY KEY(id)
);
INSERT INTO users (name,date_added_on,timestamp_added_on) 
VALUES 
('Sheldon','2018-02-04 01:10:15','1517686815'), 
('Penny', '2018-02-20 17:20:15', '1519127415'), 
('Raj' ,'2018-03-25 19:15:30' , '1521985530'), 
('Howard', '2018-03-25 10:10:10', '1521952810'), 
('Leonard','2018-04-26 20:20:20', '1524754220') , 
('Amy','2018-04-26 12:20:20' ,'1524725420'),
('Joe','2019-01-24 01:10:15','1548272415'), 
('Chandler', '2019-01-24 14:20:15', '1548319815'), 
('Monica' ,'2019-01-25 10:16:30' , '1548391590'), 
('Phoebe', '2019-01-25 20:10:10', '1548427210'),
 ('Rachel','2019-02-04 15:20:20', '1549273820') , 
('Ross','2019-02-04 11:30:20' ,'1549260020');

Examples and Usage

Example 1 : Extracting Year From Date

We can extract Year part from the date time, SQL and output for which are given below.

SELECT EXTRACT(YEAR from date_added_on), date_added_on from users;
+----------------------------------+---------------------+
| EXTRACT(YEAR from date_added_on) | date_added_on       |
+----------------------------------+---------------------+
|                             2018 | 2018-02-04 01:10:15 |
|                             2018 | 2018-02-20 17:20:15 |
|                             2018 | 2018-03-25 19:15:30 |
|                             2018 | 2018-03-25 10:10:10 |
|                             2018 | 2018-04-26 20:20:20 |
|                             2018 | 2018-04-26 12:20:20 |
|                             2019 | 2019-01-24 01:10:15 |
|                             2019 | 2019-01-24 14:20:15 |
|                             2019 | 2019-01-25 10:16:30 |
|                             2019 | 2019-01-25 20:10:10 |
|                             2019 | 2019-02-04 15:20:20 |
|                             2019 | 2019-02-04 11:30:20 |
+----------------------------------+---------------------+

Example 2 : Extracting Month From Date

Here we extract Date part from the given date time. Below are the SQL and output for the same.

SELECT EXTRACT(MONTH from date_added_on), date_added_on from users;
+-----------------------------------+---------------------+
| EXTRACT(MONTH from date_added_on) | date_added_on       |
+-----------------------------------+---------------------+
|                                 2 | 2018-02-04 01:10:15 |
|                                 2 | 2018-02-20 17:20:15 |
|                                 3 | 2018-03-25 19:15:30 |
|                                 3 | 2018-03-25 10:10:10 |
|                                 4 | 2018-04-26 20:20:20 |
|                                 4 | 2018-04-26 12:20:20 |
|                                 1 | 2019-01-24 01:10:15 |
|                                 1 | 2019-01-24 14:20:15 |
|                                 1 | 2019-01-25 10:16:30 |
|                                 1 | 2019-01-25 20:10:10 |
|                                 2 | 2019-02-04 15:20:20 |
|                                 2 | 2019-02-04 11:30:20 |
+-----------------------------------+---------------------+

Example 3: Extracting Day from Date

We extract Day from given date time in given below SQL. Output for the same is also given below.

SELECT EXTRACT(DAY from date_added_on), date_added_on from users;
+---------------------------------+---------------------+
| EXTRACT(DAY from date_added_on) | date_added_on       |
+---------------------------------+---------------------+
|                               4 | 2018-02-04 01:10:15 |
|                              20 | 2018-02-20 17:20:15 |
|                              25 | 2018-03-25 19:15:30 |
|                              25 | 2018-03-25 10:10:10 |
|                              26 | 2018-04-26 20:20:20 |
|                              26 | 2018-04-26 12:20:20 |
|                              24 | 2019-01-24 01:10:15 |
|                              24 | 2019-01-24 14:20:15 |
|                              25 | 2019-01-25 10:16:30 |
|                              25 | 2019-01-25 20:10:10 |
|                               4 | 2019-02-04 15:20:20 |
|                               4 | 2019-02-04 11:30:20 |
+---------------------------------+---------------------+

Example 4: Extracting Hour From Date

Similarly Hour is also extracted from given date time. Refer below SQL and Output:

SELECT EXTRACT(HOUR from date_added_on), date_added_on from users;
+----------------------------------+---------------------+
| EXTRACT(HOUR from date_added_on) | date_added_on       |
+----------------------------------+---------------------+
|                                1 | 2018-02-04 01:10:15 |
|                               17 | 2018-02-20 17:20:15 |
|                               19 | 2018-03-25 19:15:30 |
|                               10 | 2018-03-25 10:10:10 |
|                               20 | 2018-04-26 20:20:20 |
|                               12 | 2018-04-26 12:20:20 |
|                                1 | 2019-01-24 01:10:15 |
|                               14 | 2019-01-24 14:20:15 |
|                               10 | 2019-01-25 10:16:30 |
|                               20 | 2019-01-25 20:10:10 |
|                               15 | 2019-02-04 15:20:20 |
|                               11 | 2019-02-04 11:30:20 |
+----------------------------------+---------------------+

Example 5: Extracting DAY_MINUTE From Date

DAY_MINUTE part will return the day along with hour and minute of that hour. For Example, if the given date is ‘2019-01-26 10:20:10’ then the DAY_MINUTE will return 261020. Below SQL and output demonstrates use of DAY_MINUTE.

SELECT EXTRACT(DAY_MINUTE from date_added_on), date_added_on from users;
+----------------------------------------+---------------------+
| EXTRACT(DAY_MINUTE from date_added_on) | date_added_on       |
+----------------------------------------+---------------------+
|                                  40110 | 2018-02-04 01:10:15 |
|                                 201720 | 2018-02-20 17:20:15 |
|                                 251915 | 2018-03-25 19:15:30 |
|                                 251010 | 2018-03-25 10:10:10 |
|                                 262020 | 2018-04-26 20:20:20 |
|                                 261220 | 2018-04-26 12:20:20 |
|                                 240110 | 2019-01-24 01:10:15 |
|                                 241420 | 2019-01-24 14:20:15 |
|                                 251016 | 2019-01-25 10:16:30 |
|                                 252010 | 2019-01-25 20:10:10 |
|                                  41520 | 2019-02-04 15:20:20 |
|                                  41130 | 2019-02-04 11:30:20 |
+----------------------------------------+---------------------+

Example 6: Extracting MINUTE_SECOND From Date

MINUTE_SECOND will return the minute and second part from the date, as shown in below SQL and Output.

SELECT EXTRACT(MINUTE_SECOND from date_added_on), date_added_on from users; 
+-------------------------------------------+---------------------+
| EXTRACT(MINUTE_SECOND from date_added_on) | date_added_on       |
+-------------------------------------------+---------------------+
|                                      1015 | 2018-02-04 01:10:15 |
|                                      2015 | 2018-02-20 17:20:15 |
|                                      1530 | 2018-03-25 19:15:30 |
|                                      1010 | 2018-03-25 10:10:10 |
|                                      2020 | 2018-04-26 20:20:20 |
|                                      2020 | 2018-04-26 12:20:20 |
|                                      1015 | 2019-01-24 01:10:15 |
|                                      2015 | 2019-01-24 14:20:15 |
|                                      1630 | 2019-01-25 10:16:30 |
|                                      1010 | 2019-01-25 20:10:10 |
|                                      2020 | 2019-02-04 15:20:20 |
|                                      3020 | 2019-02-04 11:30:20 |
+-------------------------------------------+---------------------+

Example 7: Extracting YEAR_MONTH From Date

YEAR_MONTH part will return year and month from the provided date time. See below SQL and output.

SELECT EXTRACT(YEAR_MONTH from date_added_on), date_added_on from users;
+----------------------------------------+---------------------+
| EXTRACT(YEAR_MONTH from date_added_on) | date_added_on       |
+----------------------------------------+---------------------+
|                                 201802 | 2018-02-04 01:10:15 |
|                                 201802 | 2018-02-20 17:20:15 |
|                                 201803 | 2018-03-25 19:15:30 |
|                                 201803 | 2018-03-25 10:10:10 |
|                                 201804 | 2018-04-26 20:20:20 |
|                                 201804 | 2018-04-26 12:20:20 |
|                                 201901 | 2019-01-24 01:10:15 |
|                                 201901 | 2019-01-24 14:20:15 |
|                                 201901 | 2019-01-25 10:16:30 |
|                                 201901 | 2019-01-25 20:10:10 |
|                                 201902 | 2019-02-04 15:20:20 |
|                                 201902 | 2019-02-04 11:30:20 |
+----------------------------------------+---------------------+

Extracting Part from Timestamp

For Extracting part from timestamp, we first have to convert timestamp into correct date time format and then use MySQL extract function, as shown below:

SELECT EXTRACT(MONTH from from_unixtime(timestamp_added_on)), timestamp_added_on,date_added_on from users;
+-------------------------------------------------------+--------------------+---------------------+
| EXTRACT(MONTH from from_unixtime(timestamp_added_on)) | timestamp_added_on | date_added_on       |
+-------------------------------------------------------+--------------------+---------------------+
|                                                     2 |         1517686815 | 2018-02-04 01:10:15 |
|                                                     2 |         1519127415 | 2018-02-20 17:20:15 |
|                                                     3 |         1521985530 | 2018-03-25 19:15:30 |
|                                                     3 |         1521952810 | 2018-03-25 10:10:10 |
|                                                     4 |         1524754220 | 2018-04-26 20:20:20 |
|                                                     4 |         1524725420 | 2018-04-26 12:20:20 |
|                                                     1 |         1548272415 | 2019-01-24 01:10:15 |
|                                                     1 |         1548319815 | 2019-01-24 14:20:15 |
|                                                     1 |         1548391590 | 2019-01-25 10:16:30 |
|                                                     1 |         1548427210 | 2019-01-25 20:10:10 |
|                                                     2 |         1549273820 | 2019-02-04 15:20:20 |
|                                                     2 |         1549260020 | 2019-02-04 11:30:20 |
+-------------------------------------------------------+--------------------+---------------------+

As we have used above intervals with extract function, in a similar manner we can also use other intervals like HOUR_SECOND, MINUTE_MICROSECOND etc. We just have to pass the interval as a parameter to MySQL extract function.

Using EXTRACT With Group By

While fetching data and writing queries many times we have to group data according to year, month, date and so on. Considering the above table, suppose that we have to find the number of users added per day. We have to return the count of users. We simply count the number of users and group the data by DAY (date_added_on) by using extract function as shown below:

SELECT count(id),group_concat(date_added_on) FROM usersT1 group by EXTRACT(DAY from date_added_on);
+-----------+---------------------------------------------------------------------------------+
| count(id) | group_concat(date_added_on)                                                     |
+-----------+---------------------------------------------------------------------------------+
|         3 | 2018-02-04 01:10:15,2019-02-04 15:20:20,2019-02-04 11:30:20                     |
|         1 | 2018-02-20 17:20:15                                                             |
|         2 | 2019-01-24 14:20:15,2019-01-24 01:10:15                                         |
|         4 | 2018-03-25 10:10:10,2018-03-25 19:15:30,2019-01-25 10:16:30,2019-01-25 20:10:10 |
|         2 | 2018-04-26 20:20:20,2018-04-26 12:20:20                                         |
+-----------+---------------------------------------------------------------------------------+


It is clear from the above output that count of users has been returned based on the users added on distinct days. For example, 3 users are added on date ‘2019-02-04’.

In the similar manner, we can also find the count of users added per Year, as shown below.

SELECT count(id),group_concat(date_added_on) FROM usersT1 group by EXTRACT(YEAR from date_added_on);
+-----------+-------------------------------------------------------------------------------------------------------------------------+
| count(id) | group_concat(date_added_on) |
+-----------+-------------------------------------------------------------------------------------------------------------------------+
| 6 | 2018-02-04 01:10:15,2018-02-20 17:20:15,2018-03-25 19:15:30,2018-03-25 10:10:10,2018-04-26 20:20:20,2018-04-26 12:20:20 |
| 6 | 2019-02-04 15:20:20,2019-01-25 20:10:10,2019-01-25 10:16:30,2019-01-24 14:20:15,2019-01-24 01:10:15,2019-02-04 11:30:20 |
+-----------+-------------------------------------------------------------------------------------------------------------------------+

We see from the output that 6 users are added per year.

Using EXTRACT With Where Clause

You can also use extract() function with where clause if you want your data according to any valid interval that extract expects. Now, suppose that we are required the data of all users who are added in the year 2019. In such cases we can make use of below SQL:

select * from users where extract(Year from date_added_on) = 2019;
+----+----------+---------------------+--------------------+
| id | name     | date_added_on       | timestamp_added_on |
+----+----------+---------------------+--------------------+
|  7 | Joe      | 2019-01-24 01:10:15 |         1548272415 |
|  8 | Chandler | 2019-01-24 14:20:15 |         1548319815 |
|  9 | Monica   | 2019-01-25 10:16:30 |         1548391590 |
| 10 | Phoebe   | 2019-01-25 20:10:10 |         1548427210 |
| 11 | Rachel   | 2019-02-04 15:20:20 |         1549273820 |
| 12 | Ross     | 2019-02-04 11:30:20 |         1549260020 |
+----+----------+---------------------+--------------------+

Above output clearly shows the data of all the users added in year 2019.

Conclusion

In this post we discussed and learned about MySQL extract function. We saw examples as of how to use it and used it with group by and where clause too. You can make use of extract() function according to your needs.

You can learn about Extract() function from here too. Please read more articles on MySQL on Concatly. Kindly leave your valuable comments below to help me improve this resource.

Spread the Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *