Extract a named field from a date, timestamp, or interval (functional form of EXTRACT).
DATE_PART(field, source)## Overview Extracts a named field from a DATE, TIMESTAMP, or INTERVAL. DATE_PART is the functional form of EXTRACT: the field is passed as a string argument rather than a keyword. The two forms are interchangeable and produce identical results. Use DATE_PART when the field name is dynamic (for example stored in a metadata column) or when the function-call syntax is more convenient for code generation. Use EXTRACT when writing fixed-field code for maximum portability. ## Behavior - Returns a DOUBLE for most fields. Fields like YEAR, MONTH, DAY return whole numbers representable as INTEGER but are typed as DOUBLE for consistency with SECOND and EPOCH. - Returns NULL if the source is NULL. - The field argument is case-insensitive. - Supports DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIME, TIME WITH TIME ZONE, and INTERVAL sources. - Unknown field names raise an error. ## Timezone handling - For TIMESTAMP WITH TIME ZONE sources, the instant is converted to the session zone before the field is extracted. - DATE_PART('timezone', tstz) returns the zone offset in seconds east of UTC. ## Format specifiers - **'year'**: four-digit Gregorian year. - **'isoyear'**: ISO 8601 week-year. - **'quarter'**: 1 through 4. - **'month'**: 1 through 12. - **'week'**: ISO 8601 week number. - **'day'**: day-of-month. - **'dow'**: day-of-week (Sunday = 0). - **'isodow'**: day-of-week (Monday = 1). - **'doy'**: day-of-year. - **'hour'**, **'minute'**, **'second'**: time components. - **'millisecond'**, **'microsecond'**: seconds times 1000 or 1000000 plus fraction. - **'epoch'**: seconds since 1970-01-01 UTC (for timestamps) or total interval duration in seconds (for intervals). - **'timezone'**: zone offset in seconds. ## Compatibility - Functional equivalent of EXTRACT(field FROM source). Both forms are widely supported in analytical SQL engines.
| Name | Type | Description |
|---|---|---|
field | Specifies the field to extract as a string. Supported values include 'year', 'isoyear', 'quarter', 'month', 'week', 'day', 'dow', 'isodow', 'doy', 'hour', 'minute', 'second', 'millisecond', 'microsecond', 'epoch', 'timezone', 'timezone_hour', 'timezone_minute'. Case-insensitive. | |
source | Specifies the date, timestamp, or interval from which to extract the field. |
-- Year from a DATE
SELECT DATE_PART('year', DATE '2025-07-04') AS yr;-- Hour from a TIMESTAMP
SELECT DATE_PART('hour', TIMESTAMP '2025-03-15 14:30:00') AS hr;-- Day-of-week (0 = Sunday)
SELECT DATE_PART('dow', DATE '2025-03-15') AS dow;-- Epoch seconds from a timestamp
SELECT DATE_PART('epoch', TIMESTAMP '2025-01-01 00:00:00') AS epoch_s;-- Group orders by extracted month
SELECT DATE_PART('month', order_date) AS mo, COUNT(*) AS orders
FROM commerce.sales.orders
GROUP BY mo
ORDER BY mo;-- Fractional seconds from a high-precision timestamp
SELECT DATE_PART('second', TIMESTAMP '2025-03-15 14:30:45.123456') AS sec;