TIMESTAMP vs TIMESTAMPTZ

Concepts covered: Timezones

A TIMESTAMP is a reading off a wall clock with the clock's location left out. 2024-03-15 09:00:00 says a clock showed 9 in the morning, and nothing about which clock. A warehouse in Tokyo and a warehouse in New Jersey both record 2024-03-15 09:00:00 for a morning pickup, and those 2 pickups are 13 hours apart. TIMESTAMP WITH TIME ZONE, which this section's title and many engineers shorten to TIMESTAMPTZ, keeps the clock's location with the reading: 2024-03-15 09:00:00 Asia/Tokyo. With the zone attached, the value names one instant on the global timeline, and 2 such values can be ordered or subtracted no matter where they were recorded.

Same digits, different instants

The shipments table records every pickup twice. pickup_local is what the warehouse clock showed. pickup_stamped is the same reading with the warehouse's zone appended. Both are stored as text, so each query casts the column it needs. The 2 tabs below differ in which column they cast and sort by. Pick the order you expect from each before you switch.

Wall clock

Casting to TIMESTAMP keeps the digits and nothing else, so the 3 pickups at 09:00 tie and the sort falls back to shipment_id.

SELECT
  shipment_id,
  warehouse,
  CAST(pickup_local AS TIMESTAMP) AS picked_up
FROM shipments
ORDER BY picked_up, shipment_id

With zone

Casting to TIMESTAMP WITH TIME ZONE turns each reading into an instant, and the sort puts Tokyo first, then both Berlin pickups, then New York, 13 hours after Tokyo.

SELECT
  shipment_id,
  warehouse,
  CAST(pickup_stamped AS TIMESTAMP WITH TIME ZONE) AS picked_up
FROM shipments
ORDER BY picked_up

With zones, shipment 4 drops from first to second: its 07:30 in Berlin is 06:30 UTC, and Tokyo's 09:00 is 00:00 UTC, which puts the Tokyo pickup 6 and a half hours earlier. Without zones the engine has no basis for that move, because the values it compares are the digits 07:30 and 09:00 and nothing more. Neither cast is wrong. The wall-clock version answers 'which pickups happened before the morning break', and the zoned version answers 'which pickup happened first'. The type you choose for a column states which of those questions it can answer.

Every example in this lesson and the next section uses a date in March 2024, so the offsets above are the ones to use when predicting a result.

A table has 2 rows, 2024-03-15 20:00:00 Europe/London and 2024-03-15 15:00:00 America/New_York, both cast to TIMESTAMP WITH TIME ZONE. How do the 2 instants compare?

• They are the same instant, because 20:00 in London is 15:00 in New York
• The New York row is earlier by 5 hours, the difference in the printed clock readings
• The New York row is earlier by 1 hour
• The London row is earlier by 1 hour

What each type stores

In this engine a TIMESTAMP WITH TIME ZONE holds 2 things: the instant, as a count of milliseconds since 1970 in UTC, and a short key naming the zone. The zone is kept so the value can be printed back the way it was recorded, and so arithmetic on it can follow that zone's daylight rules. Some systems keep only the UTC instant and drop the zone on write, so their values read back in the session's zone, and the original zone survives only if a column of its own holds it. Casting a text value that ends in a zone name attaches both parts. Casting text with no zone produces a plain TIMESTAMP, and no zone is guessed. When a table is designed, an event that happened once, a login, a payment, a pickup, belongs in TIMESTAMP WITH TIME ZONE. A time that repeats on a local clock belongs in TIMESTAMP, with the zone in a separate column if it matters.

Shipment 1 is 2024-03-15 10:00:00 Asia/Tokyo, shipment 2 is 2024-03-15 02:00:00 Europe/Berlin and shipment 3 is 2024-03-14 20:00:00 America/New_York. Which row does this query return first?

SELECT
  shipment_id,
  CAST(pickup_stamped AS TIMESTAMP WITH TIME ZONE) AS picked_up
FROM shipments
ORDER BY picked_up

• Shipment 3, because its 20:00 New York reading is 00:00 UTC, an hour before the others
• Shipment 2, because 02:00 is the smallest clock reading of the 3 rows
• Shipment 1, because Tokyo runs furthest ahead of UTC and its day starts first
• Shipment 3, because a March 14 reading sorts before any March 15 reading

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.