Skip to main content

​
Overview

The EXTRACT() function retrieves a specified part (field) from a given date/time or interval value. It is commonly used to obtain components such as year, month, day, hour, etc., from timestamps or dates.

​
Syntax

EXTRACT (field FROM source)

​
Parameters

  • field: string or identifier specifying the part of the date / time to extract
  • source: date / time value from which to extract the specifed field
The table below shows the supported input and corresponding return types for the EXTRACT() function:
Input Type: sourceSupported field valuesReturn Type
TIMESTAMPYEAR, MONTH, DAY, HOUR, MINUTE, SECONDDOUBLE PRECISION
TIMESTAMPTZYEAR, MONTH, DAY, HOUR, MINUTE, SECONDDOUBLE PRECISION
DATEYEAR, MONTH, DAYINTEGER
The SECOND field returns a fractional value as DOUBLE PRECISION to include fractional seconds, not an integer type

​
Examples

​
EXTRACT() with Timestamp - Year

The below example uses the EXTRACT() function to extract a given timestamp’s YEAR: 
SELECT EXTRACT(YEAR FROM TIMESTAMP '2025-12-31 13:30:15.123456');
The final output will be as follows: 
+----------+
| extract  |
+----------+
| 2025     |
+----------+

​
EXTRACT() with Timestamp - Month

Here we will use the EXTRACT() function to extract a given timestamp’s MONTH: 
SELECT EXTRACT(MONTH FROM TIMESTAMP '2025-12-31 13:30:15.123456');
The final output will take the month’s part of a given timestamp: 
+----------+
| extract  |
+----------+
| 12       |
+----------+

​
EXTRACT() with Timestamp - Seconds (including fractional seconds)

Here we will use the EXTRACT() function to extract a given timestamp’s SECONDS: 
SELECT EXTRACT(SECOND FROM TIMESTAMP '2025-12-31 13:30:15.123456');
The final output will take the seconds’ part of a given timestamp: 
+----------+
| extract  |
+----------+
| 15.123456|
+----------+