MySQL - EXTRACT() Function



The MySQL EXTRACT() function is used to retrieve and return a specific part (unit) of a given date or datetime expression.

Usually, the date and time values stored in database are stored in a certain format divided by units like YEAR, MONTH, DAY, HOURS, MINUTES, SECONDS etc. Any of these units can be extracted using the EXTRACT() function in MySQL.

Syntax

Following is the syntax of MySQL EXTRACT() function −

EXTRACT(unit FROM expr);

Parameters

This method accepts two parameters. The same is described below −

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

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

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.

Return value

This function returns a specific part of a given date or datetime expression.

Example

In the following query, we are using the MySQL EXTRACT() function to extract and return the year component from the given date value −

SELECT EXTRACT(YEAR FROM '2019-05-25') As Result;

Following output is obtained −

Result
2019

Here, we are extracting the day component from the date value −

SELECT EXTRACT(DAY FROM '1998-11-21') As Result;

Following output is obtained −

Result
21

Example

We can also pass the date-time expression as an argument to this function. Here, we are extracting the minute component from the datetime value −

SELECT EXTRACT(MINUTE FROM '2015-09-05 09:40:45.2300') 
As Result;

Following output is obtained −

Result
40

Here, we are extracting the hour component from the below datetime value −

SELECT EXTRACT(HOUR FROM '2015-09-05 09:40:45.2300') As Result;

Following output is obtained −

Result
9

Example

In the below query, we are fetching the HOUR_MINUTE component from the given datetime value −

SELECT EXTRACT(HOUR_MINUTE FROM '2018-05-23 20:40:32.88558') 
As Result;

This will produce the following result −

Result
2040

Here, we are fetching the YEAR_MONTH component from the given datetime value −

SELECT EXTRACT(YEAR_MONTH FROM '2018-05-23 20:40:32.88558') 
As Result;

This will produce the following result −

Result
201805

Example

Let us create a table named ORDERS using CREATE statement as shown below −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Now, insert the following records into the ORDERS table using the INSERT statement −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Execute the below query to fetch all the inserted records in the above-created table −

Select * From ORDERS;

Following is the ORDERS table −

OID DATE CUSTOMER_ID AMOUNT
102 2009-10-08 00:00:00 3 3000.00
100 2009-10-08 00:00:00 3 1500.00
101 2009-11-20 00:00:00 2 1560.00
103 2008-05-20 00:00:00 4 2060.00

Now, we use the MySQL EXTRACT() function to extract the year from all the datetime values in the DATE column of ORDERS table −

SELECT OID, DATE,
EXTRACT(YEAR FROM DATE) as Year FROM ORDERS;

Output

The output is displayed as follows −

OID DATE Year
102 2009-10-08 00:00:00 2009
100 2009-10-08 00:00:00 2009
101 2009-11-20 00:00:00 2009
103 2008-05-20 00:00:00 2008
Advertisements