TIMESTAMPTZ, or TIMESTAMP WITH TIME ZONE, records a specific moment in time. Use it for events whose elapsed duration or cross-zone meaning matters: ticket opening and closing times, expiry dates, and time-series records. PostgreSQL accepts input with different zone offsets, normalizes the moment for time arithmetic, and displays it in the client’s time zone. section 3.1
A tempting mistake is to store UTC-looking values in TIMESTAMP WITHOUT TIME ZONE. The database then has no knowledge that the values represent UTC and cannot safely convert zones. This breaks especially badly across daylight saving time: when London’s clocks fall back, two local 1:00 a.m. times occur with different offsets. A zone-free timestamp is only a date-and-clock reading; it lacks the context needed to establish elapsed time. section 3.1 section 3.9
Calculate duration across a DST boundary
Insert the opening time as 2023-10-28 16:00 PDT and the closing time as 2023-10-29 09:00 GMT. With TIMESTAMPTZ, PostgreSQL shows the stored values as 2023-10-29 00:00:00+01 and 2023-10-29 09:00:00+00 in a London client. Subtracting them returns 10:00:00—the actual elapsed time—rather than a misleading wall-clock result. section 3.1
INSERT INTO support.tickets (content, status, opened_at, closed_at) VALUES
('Kindly close our account, as we don't need it anymore. Thank you',
'10',
'2023-10-28 16:00 PDT',
'2023-10-29 09:00 GMT');
This insert supplies explicit PDT and GMT offsets, allowing PostgreSQL to retain one comparable instant for each endpoint. section 3.1
Client display does not change the stored moment. Request a particular presentation zone with AT TIME ZONE, such as PDT, when reporting ticket opening and closing times. TIMESTAMPTZ and TIMESTAMP both use 8 bytes, so choosing the zone-aware type does not add storage cost. For a time-of-day value with an offset but no date, prefer TIMESTAMPTZ instead of TIMETZ: time zones need a date to account for DST, and TIMETZ cannot be subtracted for time math. section 3.1 section 3.2
Treat timestamp arithmetic as behavior worth testing during upgrades. Adding a fractional-year interval to a timestamptz changed between PostgreSQL releases: before PostgreSQL 15, fractional units greater than months were truncated to months; PostgreSQL 15 rounds them to the nearest month. Read every intervening major release note when skip-upgrading, especially where expiry or billing logic depends on interval calculations. section 10.1
During migrations, do not map another system’s DATETIME merely by name. Many such types lack time-zone support and must be treated as naive local timestamps before conversion to PostgreSQL TIMESTAMP WITH TIME ZONE. Check the originating type’s definition, accuracy, and accepted values; use PostgreSQL conversion functions and migration tools where appropriate. section 10.5
