Time zones and UTC handling

Concepts covered: Timezones

A system that runs in several regions collects clock readings from several zones, and a report that groups them by day needs every reading on 1 scale. The scale is UTC: the reference time with no daylight saving and no political changes, which every zone is defined as an offset from. The working rule is to convert to UTC on the way into storage, keep every stored event in UTC, and convert back to a local zone only at the moment a human reads the number.

AT TIME ZONE changes the reading and keeps the instant

AT TIME ZONE 'UTC' takes a TIMESTAMP WITH TIME ZONE and returns the same instant expressed in the zone named, so the digits change and the point on the timeline stays where it was. The logins table below holds 4 readings from 4 zones as text. The animation casts each one and converts it to UTC; watch how the row with the latest clock reading on March 15, Sydney at 23:40, becomes the earliest instant of the 4.

SELECT
  user_id,
  login_at,
  CAST(login_at AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'UTC' AS login_utc
FROM logins
ORDER BY login_utc

Kolkata's offset is 5 hours and 30 minutes, so 18:15 becomes 12:45. Newfoundland and parts of Australia also use half-hour offsets, and Nepal runs 5 hours and 45 minutes ahead of UTC, which is 1 reason a conversion should always go through a zone name rather than hand arithmetic. The London reading, 01:05 on March 16, stays as it is because London sits on UTC until the last Sunday of March.

Converting back for the reader

Stored in UTC, an event converts to any zone on demand, and the same AT TIME ZONE does the job in the other direction. The payments table keeps paid_at in UTC. The query shows each payment as the Chicago finance team and the Tokyo team would see it. Payment 2 is the one to watch: it moves to a different calendar day in Chicago.

SELECT
  payment_id,
  CAST(paid_at AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'America/Chicago' AS chicago_time,
  CAST(paid_at AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'Asia/Tokyo' AS tokyo_time
FROM payments
ORDER BY payment_id

Payment 2 happened at 03:30 UTC on March 16. In Chicago it is 22:30 on March 15, and in Tokyo it is 12:30 on March 16. A daily revenue report built in Chicago counts it on the 15th, one built in Tokyo counts it on the 16th, and both are right for their readers. The mistake is to store the Chicago reading and let the Tokyo team add hours to it, because then Chicago's daylight saving rules leak into Tokyo's numbers twice a year. Keep the UTC value and derive each view from it.

A payment is stored as 2024-03-16 02:30:00 UTC. What does AT TIME ZONE 'America/Los_Angeles' return for it, given Los Angeles is 7 hours behind UTC on that date?

• 2024-03-16 09:30:00 America/Los_Angeles
• 2024-03-16 02:30:00 America/Los_Angeles
• 2024-03-15 18:30:00 America/Los_Angeles
• 2024-03-15 19:30:00 America/Los_Angeles

When the zone was never recorded

A plain TIMESTAMP cannot be converted on its own, because AT TIME ZONE needs to know which zone the reading came from before it can move it. The repair takes 2 steps: attach the zone the clock was in, then convert. with_timezone does the first step. You give it a TIMESTAMP and a zone name, and it returns a TIMESTAMP WITH TIME ZONE with the digits unchanged. Which zone to attach is a fact about the source system, found in its configuration or its documentation, and never in the data itself.

SELECT
  order_id,
  with_timezone(CAST(ordered_local AS TIMESTAMP), 'America/New_York') AT TIME ZONE 'UTC' AS ordered_utc
FROM legacy_orders

Do

• Store event times as TIMESTAMP WITH TIME ZONE in UTC
• Convert to a local zone in the final SELECT, for display
• Name zones by region, such as Europe/Berlin
• Write down the source zone of every legacy feed

Avoid

• Add or subtract fixed hours to shift a zone
• Store a local reading without its zone
• Use an offset such as +01:00 as if it were a zone
• Assume a column holds UTC from its name alone

About this lesson

This section is part of the Data Types: 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.