EXTRACT

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: source	Supported field values	Return Type
TIMESTAMP	YEAR, MONTH, DAY, HOUR, MINUTE, SECOND	DOUBLE PRECISION
TIMESTAMPTZ	YEAR, MONTH, DAY, HOUR, MINUTE, SECOND	DOUBLE PRECISION
DATE	YEAR, MONTH, DAY	INTEGER

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