gotchasqlpostgresqlMajor
timestamp with time zone vs timestamp without time zone
Viewed 0 times
timestamptztimestamp with time zoneUTCtimezoneAT TIME ZONEdate_trunc timezone
Problem
Storing timestamps as timestamp (without time zone) in a PostgreSQL database that has multiple application servers in different time zones causes ambiguous data: the stored value has no zone context and can be misinterpreted on read.
Solution
Always use timestamptz (timestamp with time zone) for wall-clock times:
-- timestamptz stores UTC internally; displays in session timezone:
CREATE TABLE events (
id bigserial PRIMARY KEY,
occurred_at timestamptz NOT NULL DEFAULT now()
);
-- Set session timezone for display (storage is always UTC):
SET timezone = 'America/New_York';
SELECT occurred_at FROM events; -- displayed in Eastern time
-- Convert between zones:
SELECT occurred_at AT TIME ZONE 'UTC' AS utc_time,
occurred_at AT TIME ZONE 'Asia/Tokyo' AS tokyo_time
FROM events;
-- timestamp (no tz) just stores the literal value with no conversion:
-- Avoid for anything users interact with across timezones
-- timestamptz stores UTC internally; displays in session timezone:
CREATE TABLE events (
id bigserial PRIMARY KEY,
occurred_at timestamptz NOT NULL DEFAULT now()
);
-- Set session timezone for display (storage is always UTC):
SET timezone = 'America/New_York';
SELECT occurred_at FROM events; -- displayed in Eastern time
-- Convert between zones:
SELECT occurred_at AT TIME ZONE 'UTC' AS utc_time,
occurred_at AT TIME ZONE 'Asia/Tokyo' AS tokyo_time
FROM events;
-- timestamp (no tz) just stores the literal value with no conversion:
-- Avoid for anything users interact with across timezones
Why
timestamptz stores the absolute moment in UTC. On insert, PostgreSQL converts the provided value from the session timezone to UTC. On read, it converts back to the session timezone. timestamp (no tz) performs no conversion and stores the literal text value, which becomes ambiguous across timezones.
Gotchas
- AT TIME ZONE applied to timestamptz returns a timestamp (no tz) in that zone — the result loses zone info
- The PostgreSQL server timezone (postgresql.conf: timezone) affects display but never storage for timestamptz
- date_trunc on a timestamptz respects the session timezone; results vary by zone for daily/weekly truncations
- now() returns timestamptz; current_timestamp is an alias; both return the transaction start time
Code Snippets
date_trunc by day in a specific timezone using AT TIME ZONE
-- Truncate to day in user's local timezone:
SELECT date_trunc('day', occurred_at AT TIME ZONE 'America/Chicago') AS local_day,
count(*)
FROM events
GROUP BY 1
ORDER BY 1;Context
Applications with users or servers in multiple time zones storing event or record timestamps
Revisions (0)
No revisions yet.