Construct a TIMESTAMP WITH TIME ZONE from components with an optional zone name.
MAKE_TIMESTAMPTZ(year, month, day, hour, min, sec [, tz])## Overview Constructs a TIMESTAMP WITH TIME ZONE by interpreting the given date and time components as local wall-clock time in a specified time zone. The result is stored internally as a UTC instant; displayed values are rendered in the session zone. Use MAKE_TIMESTAMPTZ when you have wall-clock components plus a known source zone (for example event timestamps captured at various regional offices) and need to convert them into zone-aware instants for consistent comparison. ## Behavior - Returns a TIMESTAMP WITH TIME ZONE. - Returns NULL if any argument is NULL. - Raises an error for invalid components or unknown time zone names. - When tz is omitted, the session time zone is used. - Fractional seconds are preserved up to the engine's precision. - The returned value compares by instant: two calls with different zones but the same underlying UTC instant are equal. ## Timezone handling - The tz argument is interpreted as an IANA zone name (for example 'America/New_York'). Abbreviations like 'EST' or 'EDT' may be accepted but are ambiguous (they do not track DST), so prefer full names. - DST transitions create edge cases: spring-forward skips an hour (the value 02:30 on the transition day does not exist), and fall-back repeats an hour (02:30 occurs twice). Engine behavior for these gap/overlap cases varies; test against your data. - The returned instant is zone-stable: arithmetic and EXTRACT(EPOCH FROM ...) do not depend on session zone. ## Compatibility - Extension beyond the SQL standard. The standard does not provide a zone-aware construction function; applications must either concatenate strings and parse with TO_TIMESTAMP or use vendor-specific constructors.
| Name | Type | Description |
|---|---|---|
year | Specifies the year component as an INTEGER. | |
month | Specifies the month component as an INTEGER in the range 1 through 12. | |
day | Specifies the day-of-month in the valid range for the given month. | |
hour | Specifies the hour component as an INTEGER in the range 0 through 23. | |
min | Specifies the minute component as an INTEGER in the range 0 through 59. | |
sec | Specifies the seconds component as a DOUBLE with fractional-second support. | |
tz | Specifies the IANA time zone name (for example 'America/New_York', 'Europe/London', 'UTC') in which the supplied components are interpreted. Defaults to the session time zone when omitted. |
-- Explicit UTC construction
SELECT MAKE_TIMESTAMPTZ(2025, 3, 15, 14, 30, 0, 'UTC') AS ts_utc;-- Same wall-clock time in a different zone
SELECT MAKE_TIMESTAMPTZ(2025, 3, 15, 9, 0, 0, 'America/New_York') AS ts_et;-- Omit tz to use the session default
SELECT MAKE_TIMESTAMPTZ(2025, 6, 1, 12, 0, 0) AS ts_session_zone;-- Demonstrate that 'same instant in two zones' compare equal
SELECT MAKE_TIMESTAMPTZ(2025, 3, 15, 12, 0, 0, 'UTC')
= MAKE_TIMESTAMPTZ(2025, 3, 15, 8, 0, 0, 'America/New_York') AS same_instant;-- Build per-region event timestamps from a calendar table
SELECT region, MAKE_TIMESTAMPTZ(yr, mo, dy, hr, mi, 0, region_tz) AS event_ts
FROM analytics.campaigns.regional_events;-- DST transition: spring-forward hour that does not exist
SELECT MAKE_TIMESTAMPTZ(2025, 3, 9, 2, 30, 0, 'America/New_York'); -- interpretation is engine-defined