Timezone Control

Concepts covered: Timezones

An instant read on 2 wall clocks

A TIMESTAMP WITH TIME ZONE value is a point on the global timeline plus the zone it should be read in. AT TIME ZONE swaps that zone and keeps the point, so the clock digits change while the moment stays where it was. Every conversion question then has 2 parts: which instant, and whose wall clock reads it.

The trips table stores each end time as text that carries its zone, such as 2024-03-04 05:50:00 UTC. A CAST to TIMESTAMP WITH TIME ZONE reads that text as an instant, AT TIME ZONE 'America/Chicago' reads the instant on a Chicago clock, and a CAST of the result to DATE keeps the Chicago calendar day.

WITH local_trips AS (
  SELECT
    trip_id,
    ended_at,
    CAST(ended_at AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'America/Chicago' AS ended_local
  FROM trips
)
SELECT
  trip_id,
  ended_at,
  ended_local,
  CAST(ended_local AS DATE) AS local_date
FROM local_trips

Trips 101 and 103 both end at 23:50 on a Sunday in Chicago, yet their UTC times of day differ by an hour: 05:50 and 04:50. Chicago ran 6 hours behind UTC before 2:00 a.m. local time on March 10, 2024. After that moment it ran 5 hours behind. Both rows also carry a UTC date 1 day later than their Chicago date, so a report grouped by UTC day books Sunday night's fares on Monday.

Named zones follow daylight saving rules

A fixed offset such as -06:00 applies the same shift all year. A named zone such as America/Chicago carries the region's rule history, so it moves from 6 to 5 hours behind UTC at the instant the clocks jumped. Both tabs below read 07:30 and 08:30 UTC on March 10, 2024, half an hour before and after Chicago's jump.

Named zone

The named zone reads 01:30 before the jump and 03:30 after it: 1 hour of UTC time covers 2 hours of Chicago clock, because 02:00 to 03:00 never happened that night.

SELECT
  TIMESTAMP '2024-03-10 07:30:00 UTC' AT TIME ZONE 'America/Chicago' AS before_jump,
  TIMESTAMP '2024-03-10 08:30:00 UTC' AT TIME ZONE 'America/Chicago' AS after_jump

Fixed offset

The offset reads 01:30 and 02:30, and 02:30 is a time no Chicago clock showed on March 10, 2024.

SELECT
  TIMESTAMP '2024-03-10 07:30:00 UTC' AT TIME ZONE '-06:00' AS before_jump,
  TIMESTAMP '2024-03-10 08:30:00 UTC' AT TIME ZONE '-06:00' AS after_jump

The jump in March has a mirror in November. On November 3, 2024, Chicago clocks reached 2:00 a.m. and fell back to 1:00 a.m., so every minute between 1:00 and 2:00 happened twice. Converting from UTC still gives a single answer per instant, as these 2 readings taken an hour apart show.

SELECT
  TIMESTAMP '2024-11-03 06:30:00 UTC' AT TIME ZONE 'America/Chicago' AS first_pass,
  TIMESTAMP '2024-11-03 07:30:00 UTC' AT TIME ZONE 'America/Chicago' AS second_pass

Both columns read 01:30, and they remain different instants, 1 hour apart. The reverse trip has no single answer: a wall-clock 01:30 on that night names 2 instants, so a pipeline that stores only local digits can no longer tell which of 2 fares came first. Keep the instant, and derive local time from it.

A zone stored on every row

AT TIME ZONE accepts a zone name written as a literal, so it can't read the zone from a column. When each row carries its own zone, route the value through Unix time, which the intermediate lesson covered: to_unixtime turns the instant into seconds since 1970-01-01 UTC, and from_unixtime(seconds, zone) builds a TIMESTAMP WITH TIME ZONE read in the zone that row names. Seconds since the epoch belong to no zone, so the instant passes through unchanged.

SELECT
  trip_id,
  city,
  from_unixtime(to_unixtime(CAST(ended_at AS TIMESTAMP WITH TIME ZONE)), zone) AS ended_local
FROM trips_global

Tokyo's trip ends on March 11 locally while its UTC stamp says March 10. The Los Angeles trip at 09:30 UTC reads 01:30, 30 minutes before that city's clocks jumped at 10:00 UTC. Each row resolves against its own zone's rules.

A fare ends at 08:45 UTC on March 10, 2024. What does this query return?

SELECT
  CAST(TIMESTAMP '2024-03-10 08:45:00 UTC' AT TIME ZONE 'America/Chicago' AS DATE) AS local_date,
  hour(TIMESTAMP '2024-03-10 08:45:00 UTC' AT TIME ZONE 'America/Chicago') AS local_hour

• 2024-03-10 and 3
• 2024-03-10 and 2
• 2024-03-09 and 3
• 2024-03-10 and 8

About this lesson

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