​
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|
+----------+