0PricingLogin▼
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.

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

All lessons in this course

  1. Date Arithmetic and INTERVAL
  2. EXTRACT, DATE_TRUNC, AGE
  3. Time Zones: TIMESTAMP vs TIMESTAMPTZ
  4. Common Date Reports (MTD, WoW, YoY)
← Back to SQL Academy