Extract a named field from a date, timestamp, or interval using SQL-standard syntax.
EXTRACT(field FROM source)## Overview Extracts a named field from a DATE, TIMESTAMP, or INTERVAL using the SQL standard EXTRACT(field FROM source) syntax. This is the portable way to access individual components of a temporal value. Most common fields have a dedicated scalar function (YEAR, MONTH, HOUR, and so on), but EXTRACT is the complete form and supports less common fields like ISOYEAR, ISODOW, EPOCH, and TIMEZONE. For most fields EXTRACT returns an INTEGER; for SECOND and EPOCH the result is a DOUBLE that includes fractional precision. ## Behavior - Returns a numeric value (INTEGER or DOUBLE depending on the field). - Returns NULL if the source is NULL. - The field name is a keyword (not a string literal) in the syntax, but is matched case-insensitively. - For SECOND and EPOCH, the result is DOUBLE PRECISION; for YEAR, MONTH, DAY, HOUR, MINUTE, QUARTER, WEEK, DOW, ISODOW, DOY, MILLISECOND, MICROSECOND, the result is INTEGER or DOUBLE depending on dialect. - Supports DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIME, TIME WITH TIME ZONE, and INTERVAL sources. ## Timezone handling - For TIMESTAMP WITH TIME ZONE sources, the instant is converted to the session zone first, then the field is taken. - EXTRACT(TIMEZONE FROM tstz) returns the offset in seconds east of UTC. - EXTRACT(HOUR FROM tstz) returns the local hour in the session zone. ## Format specifiers - **YEAR**: four-digit Gregorian year. - **ISOYEAR**: ISO 8601 week-year (may differ from YEAR at the year boundary). - **QUARTER**: 1 through 4. - **MONTH**: 1 through 12. - **WEEK**: ISO 8601 week number (1 through 53). - **DAY**: day-of-month, 1 through 31. - **DOW**: day-of-week, 0 = Sunday through 6 = Saturday. - **ISODOW**: day-of-week, 1 = Monday through 7 = Sunday. - **DOY**: day-of-year, 1 through 366. - **HOUR**: 0 through 23. - **MINUTE**: 0 through 59. - **SECOND**: 0.0 through 59.999... (DOUBLE, includes fractional seconds). - **MILLISECOND**: seconds * 1000 + fractional milliseconds. - **MICROSECOND**: seconds * 1000000 + fractional microseconds. - **EPOCH**: for timestamps, seconds since 1970-01-01 UTC; for intervals, total duration in seconds. - **TIMEZONE**: zone offset in seconds east of UTC (TIMESTAMP WITH TIME ZONE only). ## Compatibility - Defined in the SQL standard (ANSI/ISO SQL:2016). DATE_PART(field_name, source) is a functional equivalent that takes the field as a string literal.
| Name | Type | Description |
|---|---|---|
field | Specifies the field to extract. Supported keywords include YEAR, ISOYEAR, QUARTER, MONTH, WEEK, DAY, DOW, ISODOW, DOY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, EPOCH, TIMEZONE, TIMEZONE_HOUR, TIMEZONE_MINUTE. | |
source | Specifies the date, timestamp, or interval value from which to extract the field. |
-- Year from a DATE
SELECT EXTRACT(YEAR FROM DATE '2025-07-04') AS yr;-- Month from a TIMESTAMP
SELECT EXTRACT(MONTH FROM TIMESTAMP '2025-03-15 14:30:00') AS mo;-- Day-of-week (0 = Sunday through 6 = Saturday in this engine's EXTRACT(DOW) convention)
SELECT EXTRACT(DOW FROM DATE '2025-03-15') AS dow;-- Fractional seconds (returns a DOUBLE)
SELECT EXTRACT(SECOND FROM TIMESTAMP '2025-03-15 14:30:45.123') AS s;-- Epoch seconds (total seconds since 1970-01-01 UTC)
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2025-01-01 00:00:00') AS epoch_s;-- Group revenue by extracted quarter
SELECT EXTRACT(QUARTER FROM order_date) AS q, SUM(total_amount) AS revenue
FROM commerce.sales.orders
GROUP BY q
ORDER BY q;