SQL Academy · Lesson

Time Zones: TIMESTAMP vs TIMESTAMPTZ

Understand the difference between TIMESTAMP and TIMESTAMPTZ, convert with AT TIME ZONE, and store UTC for portable apps.

Time Zones: TIMESTAMP vs TIMESTAMPTZ is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Two Timestamp Types

PostgreSQL has TWO timestamp types:

TIMESTAMP — "naive" timestamp, no zone information
TIMESTAMPTZ — timestamp with time zone (stored as UTC internally)

Rule of thumb: prefer TIMESTAMPTZ for events; TIMESTAMP for purely abstract times like "9:00 AM business hours".

How TIMESTAMPTZ Works

Internally always stored as UTC. On INSERT, your input is converted from the session TZ to UTC. On SELECT, output is converted back to the session TZ.

SET TIME ZONE 'Europe/Berlin';
INSERT INTO events (ts) VALUES ('2024-03-15 12:00:00');
-- stored as 2024-03-15 11:00:00 UTC (Berlin is UTC+1 in winter)

SET TIME ZONE 'UTC';
SELECT ts FROM events;
-- 2024-03-15 11:00:00+00

TIMESTAMP Is Lossy

A plain TIMESTAMP doesn't know the zone. "2024-03-15 12:00:00" could mean any local time. When users move zones, the meaning is lost.

AT TIME ZONE

Convert between zones for display:

SELECT ts AT TIME ZONE 'America/New_York' AS ny_time FROM events;
SELECT ts AT TIME ZONE 'Asia/Tokyo'        AS tokyo_time FROM events;

Storing UTC and Displaying Local

Best practice: store TIMESTAMPTZ, convert to user's zone in the SELECT, never the other way round.

SELECT id,
       ts AT TIME ZONE COALESCE(u.tz, 'UTC') AS local_ts
FROM events e
JOIN users u ON u.id = e.user_id;

SESSION TIME ZONE

You can set it per-session, per-user, or globally:

-- Session:
SET TIME ZONE 'Europe/Berlin';

-- Per-user (PG):
ALTER ROLE alice SET timezone TO 'Europe/Berlin';

-- Server default in postgresql.conf:
-- timezone = 'UTC'

SUPPLY ZONE WITH LITERAL

Embed a zone in the literal:

SELECT TIMESTAMPTZ '2024-03-15 12:00:00 Europe/Berlin';
SELECT '2024-03-15 12:00:00+02'::TIMESTAMPTZ;

NOW() Returns TIMESTAMPTZ

NOW(), CURRENT_TIMESTAMP always return TIMESTAMPTZ. Mixing them with TIMESTAMP columns is a common bug.

When TIMESTAMP (no zone) Is Right

For abstract or local times that genuinely don't depend on a zone — "store opens at 09:00" — use TIMESTAMP (or TIME).

CREATE TABLE store_hours (
  store_id BIGINT,
  open_at  TIME NOT NULL,
  close_at TIME NOT NULL
);

DST Transitions

TIMESTAMPTZ handles DST automatically. Adding INTERVAL works in wall-clock hours unless you cross the DST boundary — then you may shift by 23 or 25 hours.

SELECT TIMESTAMPTZ '2024-03-30 23:00:00 Europe/Berlin' + INTERVAL '5 hours';
-- Result accounts for the DST transition on March 31

Don't Store Offsets, Store Zone Names

An offset like +02:00 doesn't tell you when DST kicks in. A zone name like Europe/Berlin does. When you store a user's home zone, store the IANA name.

Recap

Default to TIMESTAMPTZ for events.

Stored as UTC, displayed in session TZ
Convert for display with AT TIME ZONE
Use TIMESTAMP (no zone) only for abstract local times

Quick Check

You're building a global SaaS that logs user events. Which timestamp type should you use for the occurred_at column?

Frequently asked questions

Is the “Time Zones: TIMESTAMP vs TIMESTAMPTZ” lesson free?

Yes — the full text of “Time Zones: TIMESTAMP vs TIMESTAMPTZ” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Time Zones: TIMESTAMP vs TIMESTAMPTZ”?

Understand the difference between TIMESTAMP and TIMESTAMPTZ, convert with AT TIME ZONE, and store UTC for portable apps. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Time Zones: TIMESTAMP vs TIMESTAMPTZ” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy