Time zones and AT TIME ZONE in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the AT TIME ZONE clause do in PostgreSQL?

It converts a timestamp from one time zone to another, adjusting the time accordingly.

intermediate

How does PostgreSQL treat a timestamp without time zone when using AT TIME ZONE?

It treats the timestamp as if it is in the specified time zone and converts it to timestamp with time zone (UTC).

beginner

What is the difference between timestamp with time zone and timestamp without time zone in PostgreSQL?

timestamp with time zone stores a moment in time globally (UTC internally), while timestamp without time zone stores a date and time without any time zone info.

intermediate

What happens if you apply AT TIME ZONE to a timestamp with time zone value?

It converts the timestamp to the specified time zone and returns a timestamp without time zone representing local time in that zone.

beginner

Why is it important to use AT TIME ZONE when working with timestamps across different regions?

Because it ensures correct conversion and interpretation of times according to local time zones, avoiding errors in scheduling or logging.

What type does timestamp without time zone AT TIME ZONE 'UTC' return?

A timestamp with time zone

B timestamp without time zone

C date

D interval

Applying AT TIME ZONE to a timestamp without time zone converts it to timestamp with time zone.

If you have a timestamp with time zone and apply AT TIME ZONE 'America/New_York', what is the result type?

A timestamp with time zone

B interval

C timestamp without time zone

D date

Applying AT TIME ZONE to timestamp with time zone converts it to timestamp without time zone in that zone.

Which of these is true about timestamp with time zone in PostgreSQL?

A It stores local time without offset

B It stores UTC time internally

C It ignores time zones

D It stores only date

PostgreSQL stores timestamp with time zone internally as UTC.

What does SELECT '2024-06-01 12:00'::timestamp AT TIME ZONE 'UTC' return?

A Error

B 2024-06-01 12:00:00 without time zone

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

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

Converts timestamp without time zone to timestamp with time zone at UTC.

Why might you use AT TIME ZONE when displaying timestamps to users?

A To convert times to the user's local time zone

B To delete time zone info

C To change date format

D To add random offsets

AT TIME ZONE helps convert timestamps to the user's local time for correct display.

Explain how PostgreSQL handles timestamp without time zone when using AT TIME ZONE and what the output type is.

Think about how local time is interpreted and converted to a global time.

Describe the difference in behavior when applying AT TIME ZONE to timestamp with time zone versus timestamp without time zone.

Consider input type and output type changes.