Time zones in Postgres — timestamptz vs timestamp

PostgreSQL has two timestamp data types: `timestamp` (without time zone) and `timestamptz` (with time zone). Despite their names, the difference is more about *how* they handle time zones than what they store. Understanding this distinction prevents some of the most common date-related bugs. For converting Unix timestamps to/from human-readable dates, the [epoch converter](/calculators/epoch) handles them. ## The two types ### `timestamp` (without time zone) Stores: a "wall clock" time. Year, month, day, hour, minute, second. Doesn't store: any time zone information. Behavior: when you insert `'2026-05-21 14:30:00'`, that's exactly what you get back. No conversion happens. Use cases: storing dates that don't have an associated time zone (e.g., "the meeting is at 3 PM" without specifying which zone). Rare; usually problematic. ### `timestamptz` (with time zone) Stores: an absolute moment in time, internally as UTC. The time zone information is stored separately as metadata. Behavior: - On INSERT: converts your input to UTC for storage - On SELECT: converts back to your session's time zone for display Use cases: 99% of all timestamp storage. Logs, events, schedules, modifications — anything with a real moment in time. ## The fundamental difference ```sql -- Set session time zone to America/New_York SET TIMEZONE = 'America/New_York'; -- Insert into both types INSERT INTO test (ts, tstz) VALUES ('2026-05-21 14:30:00', '2026-05-21 14:30:00'); -- Switch session to UTC SET TIMEZONE = 'UTC'; -- Query SELECT ts, tstz FROM test; -- ts: 2026-05-21 14:30:00 -- tstz: 2026-05-21 18:30:00 (because EDT is -4) ``` The `timestamp` value didn't change — it was stored as wall-clock and retrieved as wall-clock. The `timestamptz` value changed — Postgres converted from your insert session's zone to UTC for storage, then to your select session's zone for retrieval. For most use cases, you want `timestamptz`. The wall-clock-only behavior of `timestamp` causes bugs. ## When to use which **Use `timestamptz` for:** - All "this happened at this moment" timestamps - Created/updated/deleted timestamps - Event logs - Scheduling with absolute moments - User session tracking - Almost everything **Use `timestamp` for:** - Truly time-zone-independent dates (like a recurring meeting time you'll convert at runtime) - Imported data where the source has no time zone In practice, almost always use `timestamptz`. The "without time zone" type is a footgun in modern applications. ## Setting time zones PostgreSQL has several time zone concepts: **Server time zone**: the daemon's default. Set in `postgresql.conf`. Often UTC for production. **Session time zone**: per-connection. Set with `SET TIMEZONE`. **Per-statement time zone**: with `AT TIME ZONE`. ```sql -- Set session SET TIMEZONE = 'America/New_York'; -- Per-statement SELECT '2026-05-21 14:30:00 UTC' AT TIME ZONE 'Asia/Tokyo'; -- Returns: 2026-05-21 23:30:00 ``` Best practice: server time zone in UTC, applications convert at the display layer. ## Inserting timestamps Several ways to insert: ```sql -- ISO 8601 string (recommended) INSERT INTO events (occurred_at) VALUES ('2026-05-21T14:30:00Z'); -- Postgres-style with explicit zone INSERT INTO events (occurred_at) VALUES ('2026-05-21 14:30:00+00:00'); -- Without zone (uses session's time zone) INSERT INTO events (occurred_at) VALUES ('2026-05-21 14:30:00'); -- Above is dangerous — session zone matters -- Using NOW() INSERT INTO events (occurred_at) VALUES (NOW()); -- Using AT TIME ZONE for clarity INSERT INTO events (occurred_at) VALUES ( '2026-05-21 09:30:00' AT TIME ZONE 'America/New_York' ); -- Insert as 9:30 AM Eastern; stores as 13:30 UTC ``` Always specify the time zone in your insert if possible. Implicit session zones are bug-prone. ## Querying timestamps ```sql -- Basic SELECT (returns in session's time zone) SELECT occurred_at FROM events; -- Convert to specific zone SELECT occurred_at AT TIME ZONE 'Asia/Tokyo' FROM events; -- Format for display SELECT to_char(occurred_at, 'YYYY-MM-DD HH24:MI:SS TZ') FROM events; -- Compare with current time SELECT * FROM events WHERE occurred_at > NOW() - INTERVAL '1 day'; -- Filter by date range SELECT * FROM events WHERE occurred_at BETWEEN '2026-05-01' AND '2026-05-31'; ``` ## Comparing timestamps ```sql -- Equality SELECT * FROM events WHERE occurred_at = '2026-05-21T14:30:00Z'; -- Range SELECT * FROM events WHERE occurred_at >= '2026-05-21' AND occurred_at < '2026-05-22'; -- Time delta SELECT * FROM events WHERE occurred_at > NOW() - INTERVAL '7 days'; ``` When comparing `timestamptz` values, Postgres converts both sides to UTC and compares. Time zones don't cause comparison bugs. When comparing `timestamp` (without zone), Postgres compares wall-clock values. This can give wrong results across DST boundaries. ## DST behavior `timestamptz` handles DST automatically: ```sql -- Insert before DST SET TIMEZONE = 'America/New_York'; INSERT INTO events (occurred_at) VALUES ('2026-03-08 01:30:00 EST'); -- After DST (clocks have shifted forward) INSERT INTO events (occurred_at) VALUES ('2026-03-08 01:30:00 EDT'); -- Both are valid, distinct UTC times ``` `timestamp` doesn't: ```sql -- '2026-11-01 01:30:00' (with timestamp, no zone) is ambiguous -- (this hour happens twice in fall-back) -- Postgres just stores the wall-clock; you can't distinguish ``` Another reason to prefer `timestamptz`. ## Indexes and performance Both types index well. No performance difference between them for typical queries. The cost of `timestamptz` is minimal — Postgres internally stores both as 8-byte integers (microseconds since epoch). The "with time zone" is metadata about how to interpret the value, not a separate field. ## Common patterns ### Recording event timestamps ```sql CREATE TABLE events ( id SERIAL PRIMARY KEY, user_id INT NOT NULL, event_type VARCHAR(50) NOT NULL, occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); ``` ### User-specific local times ```sql -- Store user's preferred time zone CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(255), timezone VARCHAR(64) DEFAULT 'UTC' ); -- Display events in user's local time SELECT e.id, e.occurred_at AT TIME ZONE u.timezone AS local_time FROM events e JOIN users u ON e.user_id = u.id; ``` ### Time-windowed aggregations ```sql -- Count events per hour for the last 24 hours SELECT date_trunc('hour', occurred_at) AS hour, COUNT(*) AS count FROM events WHERE occurred_at > NOW() - INTERVAL '24 hours' GROUP BY hour ORDER BY hour; ``` ### Recurring schedules ```sql -- Use a recurring rule rather than absolute times CREATE TABLE recurring_jobs ( id SERIAL PRIMARY KEY, cron_expression VARCHAR(50) NOT NULL, timezone VARCHAR(64) DEFAULT 'UTC', last_run_at TIMESTAMPTZ ); ``` ## Common mistakes **Storing as `timestamp` instead of `timestamptz`**: leads to ambiguous timestamps across time zones. **Setting wrong session time zone**: implicit conversions go wrong. **Manually converting before insert**: instead of letting Postgres handle it. **Using `localtime` operator**: deprecated, ambiguous. Use `AT TIME ZONE`. **Forgetting that `NOW()` returns `timestamptz`**: this is usually correct, but mixed with `timestamp` columns it confuses. ## Migration: from `timestamp` to `timestamptz` If you have a column declared as `timestamp` but realize you should have used `timestamptz`: ```sql -- Migrate column ALTER TABLE events ALTER COLUMN occurred_at TYPE TIMESTAMPTZ USING occurred_at AT TIME ZONE 'UTC'; ``` This converts existing values, assuming they were UTC. If they were in a different zone, replace `'UTC'` with the correct zone. ## FAQ ### Should I always use `timestamptz`? Almost always. The only exception is if you have a strong reason to store wall-clock times explicitly, which is rare. ### What does Postgres store internally? Both types store an 8-byte integer (microseconds since 2000-01-01 00:00:00 UTC). The "with time zone" type adds metadata about how to format on output. ### Does `AT TIME ZONE` change the underlying timestamp? No — it only converts for display. The stored value is unchanged. ### What's the difference between `NOW()` and `CURRENT_TIMESTAMP`? Both return `timestamptz`. They're equivalent. ### Can I compare a `timestamp` with a `timestamptz`? Yes, but Postgres applies the session's time zone to interpret the `timestamp` first. This is bug-prone — prefer using `timestamptz` consistently. ### What about `time` and `timetz`? `time` is just hour:minute:second (no date). `timetz` adds time zone. Both are rarely useful in practice — full timestamps are usually what you want. ## Bottom line Use `timestamptz` for all timestamp columns. PostgreSQL handles time zone conversions automatically; `timestamp` (without zone) is a footgun. Set your server time zone to UTC and let your applications convert for display. Always specify time zones in inserts and use `AT TIME ZONE` for explicit conversions. For converting Unix epoch values to/from PostgreSQL timestamps, the [epoch converter](/calculators/epoch) handles them.