Cloud

EXTRACT

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, and hour 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 specified field.

This table 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

This example shows how to use the EXTRACT function to extract a given timestamp’s year:

SELECT EXTRACT(YEAR FROM TIMESTAMP '2025-12-31 13:30:15.123456');

The query returns:

+----------+
| extract  |
+----------+
| 2025     |
+----------+

EXTRACT with timestamp - month

This example uses the EXTRACT function to extract a given timestamp’s month:

SELECT EXTRACT(MONTH FROM TIMESTAMP '2025-12-31 13:30:15.123456');

The query returns the month’s part of the given timestamp:

+----------+
| extract  |
+----------+
| 12       |
+----------+

EXTRACT with timestamp - seconds (including fractional seconds)

This example uses the EXTRACT function to extract a given timestamp’s seconds, including fractional seconds:

SELECT EXTRACT(SECOND FROM TIMESTAMP '2025-12-31 13:30:15.123456');

The query returns the seconds' part of the given timestamp:

+----------+
| extract  |
+----------+
| 15.123456|
+----------+