EXTRACT

This article will assist you in using the EXTRACT() function to extract a given time part from a timestamp or interval.

Overview

The EXTRACT() function extracts a given part from a specified source. The source must be a value expression of timestamp, time, or interval.

Syntax

EXTRACT(field from source)

Let’s analyze the above syntax:

• field: used to specify the time parts that are to be extracted.
• source: used to identify a date/time value. The value type is TIMESTAMP (YYYY-MM-DD HH:MM:SS) or INTERVAL (year - month - day hour - minute - second).

Input and Return Type

The table below shows the supported input types of field and the return type of the EXTRACT() function:



Examples

#Case 1: EXTRACT() with Timestamp - Year

The below example uses the EXTRACT() function to extract a given timestamp’s YEAR:

SELECT EXTRACT(YEAR FROM TIMESTAMP '2020-12-31 13:30:15');

The final output will be as follows:

+----------+
| extract  |
+----------+
| 2020     |
+----------+

#Case 2: EXTRACT() with Timestamp - Month

Here we will use the EXTRACT() function to extract a given timestamp’s MONTH:

SELECT EXTRACT(MONTH FROM TIMESTAMP '2016-10-31 13:30:15');

The final output will take the month’s part of a given timestamp:

+----------+
| extract  |
+----------+
| 10       |
+----------+

#Case 3: EXTRACT() with Interval - Day

In this example, we will extract the DAY part of a given interval:

SELECT EXTRACT(DAY FROM INTERVAL '6 years 8 months 20 days 4 hours 47 minutes 22 second' );

The final output will be as follows:

+----------+
| extract  |
+----------+
| 20       |
+----------+

#Case 4: EXTRACT() with Interval - Second

The below example uses the EXTRACT() function to extract seconds from the given interval.

SELECT EXTRACT(SECOND FROM INTERVAL '6 years 2 months 13 days 8 hours 30 minutes 43 second' );

The final output will be as follows:

+------------+
| extract    |
+------------+
| 43.000000  |
+------------+