Time zones and AT TIME ZONE in PostgreSQL - Step-by-Step Execution

Concept Flow - Time zones and AT TIME ZONE

Start with timestamp without time zone

↓

Apply AT TIME ZONE 'zone1'

↓

Convert timestamp to timestamptz in zone1

↓

Apply AT TIME ZONE 'zone2'

↓

Convert timestamptz to timestamp without time zone in zone2

↓

Result: timestamp adjusted to zone2 time

The AT TIME ZONE operator converts timestamps between time zones by first interpreting or converting the timestamp in one zone, then shifting it to another zone.

Execution Sample

PostgreSQL

SELECT '2024-06-01 12:00:00'::timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York';

Converts a timestamp from UTC to America/New_York time zone.

Execution Table

Step	Expression	Action	Intermediate Result	Final Result
1	'2024-06-01 12:00:00'::timestamp	Interpret as timestamp without time zone	2024-06-01 12:00:00 (no zone)
2	AT TIME ZONE 'UTC'	Convert timestamp to timestamptz assuming UTC	2024-06-01 12:00:00+00
3	AT TIME ZONE 'America/New_York'	Convert timestamptz to timestamp without time zone in New York zone		2024-06-01 08:00:00
4	Result	Final timestamp adjusted to New York time		2024-06-01 08:00:00

💡 Conversion ends after applying both AT TIME ZONE operations, resulting in timestamp adjusted to target zone.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
timestamp	2024-06-01 12:00:00 (no zone)	2024-06-01 12:00:00+00	2024-06-01 08:00:00 (no zone)	2024-06-01 08:00:00

Key Moments - 2 Insights

Why does applying AT TIME ZONE twice change the timestamp value?

The first AT TIME ZONE converts the timestamp to a timestamptz assuming the given zone, adding time zone info. The second AT TIME ZONE converts that timestamptz back to a timestamp in the new zone, adjusting the time accordingly (see execution_table rows 2 and 3).

What is the difference between timestamp and timestamptz in this context?

A timestamp without time zone is just a date and time with no zone info. A timestamptz stores the absolute point in time with zone info. AT TIME ZONE converts between these types by interpreting or shifting time (see variable_tracker).

Visual Quiz - 3 Questions

Look at the execution_table, what is the intermediate result after applying AT TIME ZONE 'UTC'?

A 2024-06-01 08:00:00 (no zone)

B 2024-06-01 12:00:00 (no zone)

C 2024-06-01 12:00:00+00

D 2024-06-01 16:00:00+00

💡 Hint

Check the 'Intermediate Result' column at Step 2 in the execution_table.

At which step does the timestamp get adjusted to New York time?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Look at the 'Action' and 'Final Result' columns in the execution_table for Step 3.

If the original timestamp was '2024-06-01 12:00:00'::timestamp AT TIME ZONE 'America/Los_Angeles', what would be the intermediate timestamptz after the first AT TIME ZONE?

A 2024-06-01 19:00:00+00

B 2024-06-01 12:00:00-07

C 2024-06-01 12:00:00+00

D 2024-06-01 05:00:00+00

💡 Hint

Remember that AT TIME ZONE 'zone' converts timestamp to timestamptz assuming that zone's offset; Los Angeles is UTC-7 in June, so add 7 hours to get UTC.

Concept Snapshot

AT TIME ZONE converts timestamps between zones.
Timestamp without time zone + AT TIME ZONE 'zone' = timestamptz assuming that zone.
Timestamptz + AT TIME ZONE 'zone' = timestamp in that zone.
Use double AT TIME ZONE to shift timestamp from one zone to another.
Result is always timestamp without time zone after second conversion.

Full Transcript

This visual execution shows how PostgreSQL's AT TIME ZONE operator works. Starting with a timestamp without time zone, applying AT TIME ZONE 'UTC' converts it to a timestamptz assuming the time is UTC. Then applying AT TIME ZONE 'America/New_York' converts that timestamptz to a timestamp without time zone in New York time, adjusting the hour accordingly. Variables track the timestamp value changing from naive to timestamptz and back to naive with adjusted time. Key moments clarify why two AT TIME ZONE operations are needed to shift time zones and the difference between timestamp and timestamptz types. The quiz tests understanding of intermediate results and time zone offsets.