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.

Practice

1. What does the AT TIME ZONE clause do in PostgreSQL?

easy

A. It converts a timestamp from one time zone to another.

B. It changes the system time zone permanently.

C. It formats the timestamp as a string.

D. It deletes time zone information from a timestamp.

Solution

1. Step 1: Understand the purpose of AT TIME ZONE

   This clause is used to convert timestamps between different time zones.

2. Step 2: Compare options

   Only It converts a timestamp from one time zone to another. correctly describes this conversion. Other options describe unrelated actions.

3. Final Answer:

   It converts a timestamp from one time zone to another. -> Option A

4. Quick Check:

   AT TIME ZONE converts time zones [OK]

Hint: Remember: AT TIME ZONE changes time zones, not system settings [OK]

Common Mistakes:

• Thinking it changes system time zone permanently
• Confusing it with formatting functions
• Assuming it removes time zone info

2. Which of the following is the correct syntax to convert a timestamp to 'America/New_York' time zone in PostgreSQL?

easy

A. SELECT timestamp_column AT TIMEZONE 'America/New_York';

B. SELECT timestamp_column AT TIME ZONE 'America/New_York';

C. SELECT timestamp_column AT TIMEZONE America/New_York;

D. SELECT timestamp_column AT TIME ZONE America/New_York;

Solution

1. Step 1: Recall correct syntax for AT TIME ZONE

   The correct syntax uses two words: AT TIME ZONE followed by the time zone in single quotes.

2. Step 2: Check each option

   Only SELECT timestamp_column AT TIME ZONE 'America/New_York'; matches the correct syntax exactly. The others either use AT TIMEZONE as one word (incorrect spelling) or miss quotes around the time zone name.

3. Final Answer:

   SELECT timestamp_column AT TIME ZONE 'America/New_York'; -> Option B

4. Quick Check:

   Use AT TIME ZONE 'zone' with quotes [OK]

Hint: Use two words: AT TIME ZONE with quotes around zone [OK]

Common Mistakes:

• Writing AT TIMEZONE as one word
• Omitting quotes around time zone
• Using incorrect keyword order

3. Given the query:

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

What is the output timestamp?

medium

A. 2024-06-01 20:00:00

B. 2024-06-01 04:00:00

C. 2024-06-01 05:00:00

D. 2024-06-01 12:00:00

Solution

1. Step 1: Understand the query steps

   The first AT TIME ZONE 'UTC' converts the timestamp without time zone to UTC timestamp with time zone. The second AT TIME ZONE 'America/Los_Angeles' converts that UTC time to Los Angeles local time.

2. Step 2: Calculate time difference

   On June 1, Los Angeles is on PDT (UTC-7). So 12:00 UTC minus 7 hours is 05:00 local time. But since the query applies AT TIME ZONE twice, the final output is a timestamp without time zone in Los Angeles time, which is 05:00.

3. Final Answer:

   2024-06-01 05:00:00 -> Option C

4. Quick Check:

   UTC 12:00 minus 7 hours = 05:00 [OK]

Hint: Remember LA is UTC-7 in June; subtract 7 hours [OK]

Common Mistakes:

• Confusing the order of AT TIME ZONE conversions
• Ignoring daylight saving time offset
• Assuming output is in UTC

4. Identify the error in this query:

SELECT '2024-06-01 12:00:00'::timestamp AT TIMEZONE 'Europe/London';

medium

A. The timestamp literal is missing time zone info.

B. The query is correct and will run without error.

C. The time zone name is invalid.

D. The keyword should be AT TIME ZONE with a space.

Solution

1. Step 1: Check the syntax of AT TIME ZONE

   The query uses AT TIMEZONE as one word, which is incorrect syntax in PostgreSQL.

2. Step 2: Verify other parts

   The timestamp literal is valid, and 'Europe/London' is a valid time zone name. So no error there.

3. Final Answer:

   The keyword should be AT TIME ZONE with a space. -> Option D

4. Quick Check:

   Use two words: AT TIME ZONE [OK]

Hint: Always write AT TIME ZONE as two words [OK]

Common Mistakes:

• Writing AT TIMEZONE as one word
• Assuming timestamp needs time zone info
• Thinking time zone name is invalid

5. You have a timestamp with time zone column created_at stored in UTC. You want to display it as local time in 'Asia/Tokyo' but keep it as timestamp without time zone. Which query correctly does this?

hard

A. SELECT created_at AT TIME ZONE 'Asia/Tokyo' FROM orders;

B. SELECT created_at::timestamp AT TIME ZONE 'Asia/Tokyo' FROM orders;

C. SELECT (created_at AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Tokyo' FROM orders;

D. SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Tokyo' FROM orders;

Solution

1. Step 1: Understand data type and conversion

   created_at is timestamp with time zone in UTC. Applying AT TIME ZONE 'Asia/Tokyo' converts it to timestamp without time zone in Tokyo local time.

2. Step 2: Analyze options

   SELECT created_at AT TIME ZONE 'Asia/Tokyo' FROM orders; correctly applies AT TIME ZONE 'Asia/Tokyo' to convert UTC timestamptz to local timestamp. SELECT created_at::timestamp AT TIME ZONE 'Asia/Tokyo' FROM orders; casts to timestamp first, losing time zone info before conversion, causing wrong result. The options that apply AT TIME ZONE 'UTC' first perform extra and incorrect conversions.

3. Final Answer:

   SELECT created_at AT TIME ZONE 'Asia/Tokyo' FROM orders; -> Option A

4. Quick Check:

   timestamptz AT TIME ZONE zone = local timestamp [OK]

Hint: Apply AT TIME ZONE directly on timestamptz to get local timestamp [OK]

Common Mistakes:

• Casting timestamptz to timestamp before conversion
• Applying multiple AT TIME ZONE conversions unnecessarily
• Confusing timestamp with and without time zone