Converting With AT TIME ZONE

Concepts covered: Timezones, Timestamp Types, Date vs Timestamp, Common Table Expressions

The time zone part arrives as "daily revenue for the Tokyo office", "orders by hour of the day" or "why doesn't our month-end match finance's?" Each needs the same fact: a single instant has one UTC value and a different local date and hour in every zone, so the day a row belongs to depends on whose clock reads it.

Order A is March revenue in UTC and February revenue in Los Angeles. Order B is February revenue in UTC and March revenue in Tokyo. A month-end report that buckets on the stored value puts each in the wrong month for one region, and the same shift at June 30 moves orders across a fiscal year: an order stored at 2025-07-01 02:00 UTC was rung up at 19:00 on June 30 in Los Angeles, inside FY2025 rather than FY2026.

Which way the conversion goes

In Postgres and Redshift, AT TIME ZONE runs in opposite directions depending on its input. Applied to a TIMESTAMP WITH TIME ZONE, an instant, it returns a TIMESTAMP with no zone that holds the wall clock in the named zone. Applied to a plain TIMESTAMP, it reads the value as wall-clock time in the named zone and returns the instant. The keyword stays the same and the input type picks the direction.

-- order_ts is TIMESTAMP WITH TIME ZONE: one AT gives the local wall clock
SELECT o.order_id,
       o.order_ts AT TIME ZONE r.tz_name AS local_ts
FROM orders o
JOIN regions r ON r.region = o.region;

-- order_ts is a plain TIMESTAMP holding UTC values:
-- pin it to UTC first (giving an instant), then convert to local
SELECT o.order_id,
       (o.order_ts AT TIME ZONE 'UTC') AT TIME ZONE r.tz_name AS local_ts
FROM orders o
JOIN regions r ON r.region = o.region;

The bug shows up in the second case written with a single AT TIME ZONE. The plain value 2024-03-01 03:30 is read as 03:30 in Los Angeles, which is the instant 11:30 UTC, so the conversion moves order A 8 hours later instead of 8 hours earlier. The result sits 16 hours from the right answer, and an error of exactly twice a zone's offset is the signature of a conversion run backwards. Ask the question that prevents it before writing anything: is order_ts an instant or a wall-clock reading, and in which zone?

Convert first, then bucket

Order of operations decides the answer. Truncating to the day in UTC and converting the label afterwards relabels a UTC day: the bucket still runs midnight to midnight UTC, which in Los Angeles is 16:00 to 16:00 before the clocks change on March 10 and 17:00 to 17:00 after. A BI tool's time zone setting does the same, changing the labels it draws and never the groups the warehouse computed. The local day exists only after conversion, so convert first and only then cast or truncate before grouping.

WITH local_orders AS (
  SELECT r.region,
         CAST(o.order_ts AT TIME ZONE r.tz_name AS DATE) AS local_day,
         o.amount
  FROM orders o
  JOIN regions r ON r.region = o.region
  WHERE o.order_ts >= TIMESTAMP WITH TIME ZONE '2024-02-29 00:00+00'
    AND o.order_ts <  TIMESTAMP WITH TIME ZONE '2024-04-02 00:00+00'
)
SELECT region, local_day, SUM(amount) AS revenue
FROM local_orders
WHERE local_day >= DATE '2024-03-01'
  AND local_day <  DATE '2024-04-01'
GROUP BY region, local_day;

The padded range on the stored column keeps the scan bounded, since no zone sits more than 14 hours from UTC, and the exact filter runs on the converted date. Writing the conversion once, in the CTE, means the GROUP BY and the filter agree about which day a row is on, and so does any window function downstream.

Zone names, not offsets

Fixed offset ('-08:00')

• Right for Los Angeles only until 2024-03-10, when its clock moves to UTC-7
• Off by 1 hour for around 8 months of every year, with no error raised
• In Postgres AT TIME ZONE, 'UTC+8' follows POSIX signs and means 8 hours behind UTC

IANA zone name ('America/Los_Angeles')

• Carries every DST transition and historical rule change
• Converts each row with the offset in force at that instant
• Lives in a tz_name column on the regions table, one value per region

One more question decides the join: whose clock? A store's sales belong to the store's zone and a customer's activity streak to the customer's zone. A company-wide daily total needs one zone for everyone, usually headquarters or UTC. Each is a different source for tz_name, and stating which you picked is half the answer.

About this lesson

This section is part of the Date Arithmetic: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.