MySQL EXTRACT()

By /
EXTRACT Function

In this tutorial, we will study the MySQL EXTRACT() function. The EXTRACT() function is a combination of the date functions and others like WEEK() and QUARTER(), and the time functions and others like MICROSECOND(). The EXTRACT() function is used to extract a part from a date or datetime value. This part can be day, month, year, hour, minute and so on. Let us dwell deeper into this and the syntax in the next section.

Syntax of MySQL EXTRACT()

EXTRACT(part FROM value)Code language: SQL (Structured Query Language) (sql)

Where, ‘value’ is a date or datetime value from which a ‘part’ is to be extracted and,

‘part’ is the part parameter. This parameter specifies what is to be extracted from ‘value’. The ‘part’ parameter can have the following values –

  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR

The below ‘part’ values should be in quotes.

  • SECOND_MICROSECOND where value is of the format – ‘SECONDS.MICROSECONDS’
  • MINUTE_MICROSECOND where value is of the format – ‘MINUTES:SECONDS.MICROSECONDS’
  • MINUTE_SECOND where value is of the format – ‘MINUTES:SECONDS’
  • HOUR_MICROSECOND where value is of the format – ‘HOURS:MINUTES:SECONDS.MICROSECONDS’
  • HOUR_SECOND where value is of the format – ‘HOURS:MINUTES:SECONDS’
  • HOUR_MINUTE where value is of the format – ‘HOURS:MINUTES’
  • DAY_MICROSECOND where value is of the format – ‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’
  • DAY_SECOND where value is of the format – ‘DAYS HOURS:MINUTES:SECONDS’
  • DAY_MINUTE where value is of the format – ‘DAYS HOURS:MINUTES’
  • DAY_HOUR where value is of the format – ‘DAYS HOURS’
  • YEAR_MONTH where value is of the format – ‘YEARS-MONTHS’

Examples of MySQL EXTRACT()

Let us kick things off with a basic example. Let us use the EXTRACT() function to extract the day value from the date – ‘2021-03-15’. We will use the SELECT statement and an alias called ‘Result’ to make the output readable. The query is –

SELECT EXTRACT(DAY FROM '2021-03-15') AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL EXTRACT Basic Example

Date Functions Using EXTRACT()

Let us implement a query using the EXTRACT() function that returns the date parts of the date value. We will return the Day, month, week, and year values. The query is –

SET @date='2020-10-25'; 
SELECT @date AS Date, 
EXTRACT(DAY FROM @date) AS Day, 
EXTRACT(WEEK FROM @date) AS WEEK, 
EXTRACT(MONTH FROM @date) AS Month, 
EXTRACT(YEAR FROM @date) AS Year;Code language: SQL (Structured Query Language) (sql)

And the output is –

Extract Date Values 1
MySQL EXTRACT Date Values

As you know, another way to do this is using the DAY(), MONTH(), WEEK() and YEAR() functions.

Time Functions Using MySQL EXTRACT()

Next, let us use MySQL EXTRACT() to get the time parts from a datetime value. We will return the hour, minute, seconds and microseconds values. The query is –

SET @datetime = '2021-04-02 14:25:16.12533'; 
SELECT @datetime AS Datetime, 
EXTRACT(HOUR FROM @datetime) AS Hour, 
EXTRACT(MINUTE FROM @datetime) AS Minute, 
EXTRACT(SECOND FROM @datetime) AS Seconds, 
EXTRACT(MICROSECOND FROM @datetime) AS Microseconds;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL EXTRACT Time Values

Again, another way to do this is using the HOUR(), MINUTE(), SECOND() and MICROSECOND() functions.

EXTRACT() With CURDATE()

Let us use the CURDATE() function with the EXTRACT() function and extract the current day value. Since CURDATE() returns a date value, we can use it with the EXTRACT() function. The query is –

SELECT CURDATE() AS Today, EXTRACT(DAY FROM CURDATE()) AS Day;Code language: SQL (Structured Query Language) (sql)

And the output is –

Extract Curdate

MySQL EXTRACT() With NOW()

Moving on, let us use the MySQL EXTRACT() function with the NOW() function and extract the hour value and month value from the result returned by NOW(). Since NOW() returns a datetime value, we can use it with the NOW() function. The query is –

SELECT NOW() AS 'Current Date & Time', 
EXTRACT(HOUR FROM NOW()) AS Hour, 
EXTRACT(MONTH FROM NOW()) AS Month;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL EXTRACT Now

Working With Tables

Consider the below BusStop Table.

Extract Busstop Table
BusStop Table

Let us write a query that will extract the month, day, hour and minute values from the DepartureTime column and display it along with the bus number and name. The query is –

SELECT Bus_No, Name, 
EXTRACT(MONTH FROM DepartureTime) AS Month, 
EXTRACT(DAY FROM DepartureTime) AS Day, 
EXTRACT(HOUR FROM DepartureTime) AS Hour, 
EXTRACT(MINUTE FROM DepartureTime) AS Minute FROM BusStop;Code language: SQL (Structured Query Language) (sql)

And the output is –

Extract Table Example

Conclusion

The MySQL EXTRACT() function is widely used while retrieving any part from a date/datetime value. I would encourage you to practice different examples of it.

References