Time zones and AT TIME ZONE in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Convert timestamp to a different time zone

Given the timestamp '2024-06-01 15:00:00' in UTC, what is the result of converting it to 'America/New_York' using AT TIME ZONE in PostgreSQL?

PostgreSQL

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

A 2024-06-01 15:00:00

B 2024-06-01 20:00:00

C 2024-06-01 10:00:00

D 2024-06-01 11:00:00

💡 Hint

Remember that New York is UTC-4 during daylight saving time in June.

The timestamp is first treated as UTC, then converted to America/New_York time zone, which is UTC-4 in June, so 15:00 UTC becomes 11:00 local time.

❓ query_result

intermediate

Effect of AT TIME ZONE on timestamp with time zone

What is the output of this query? SELECT '2024-06-01 15:00:00+00'::timestamptz AT TIME ZONE 'America/Los_Angeles';

PostgreSQL

SELECT '2024-06-01 15:00:00+00'::timestamptz AT TIME ZONE 'America/Los_Angeles';

A 2024-06-01 08:00:00

B 2024-06-01 22:00:00

C 2024-06-01 15:00:00

D 2024-06-01 07:00:00

💡 Hint

Los Angeles is UTC-7 during daylight saving time in June.

The timestamptz is converted to the local time in America/Los_Angeles by subtracting 7 hours from UTC, so 15:00 UTC becomes 08:00 local time.

📝 Syntax

advanced

Identify the syntax error in time zone conversion

Which option contains a syntax error when converting timestamp to a time zone in PostgreSQL?

A SELECT '2024-06-01 15:00:00'::timestamp AT TIME ZONE 'UTC';

B SELECT '2024-06-01 15:00:00'::timestamp AT TIMEZONE 'UTC';

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

D SELECT '2024-06-01 15:00:00+00'::timestamptz AT TIME ZONE 'UTC';

💡 Hint

Check the exact spelling and spacing of the AT TIME ZONE clause.

The keyword must be exactly 'AT TIME ZONE' with spaces. 'AT TIMEZONE' without space is invalid syntax.

❓ optimization

advanced

Optimizing queries with multiple time zone conversions

Which query is the most efficient way to convert a UTC timestamp to 'Europe/Paris' local time and then back to UTC in PostgreSQL?

A SELECT (ts AT TIME ZONE 'UTC') AT TIME ZONE 'Europe/Paris' AT TIME ZONE 'UTC' FROM (SELECT '2024-06-01 12:00:00'::timestamp AS ts) sub;

B SELECT ts AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Paris' AT TIME ZONE 'UTC' FROM (SELECT '2024-06-01 12:00:00'::timestamp AS ts) sub;

C SELECT (ts AT TIME ZONE 'Europe/Paris') AT TIME ZONE 'UTC' FROM (SELECT '2024-06-01 12:00:00+00'::timestamptz AS ts) sub;

D SELECT ts AT TIME ZONE 'Europe/Paris' FROM (SELECT '2024-06-01 12:00:00+00'::timestamptz AS ts) sub;

💡 Hint

Consider the data types and the order of conversions to avoid redundant operations.

Option C converts timestamptz to Europe/Paris local time and then back to UTC efficiently. Other options have redundant or incorrect conversions.

🧠 Conceptual

expert

Understanding AT TIME ZONE behavior with timestamp and timestamptz

Which statement correctly describes the behavior of AT TIME ZONE in PostgreSQL when applied to timestamp without time zone and timestamp with time zone types?

A For timestamp without time zone, AT TIME ZONE treats the timestamp as local time in the given zone and converts it to UTC; for timestamptz, it converts UTC to the given time zone local time.

B For timestamp without time zone, AT TIME ZONE converts the timestamp to UTC ignoring the given zone; for timestamptz, it converts UTC to local time in the given zone.

C For timestamp without time zone, AT TIME ZONE converts the timestamp to the given time zone local time; for timestamptz, it converts local time to UTC.

D For both timestamp and timestamptz, AT TIME ZONE converts the timestamp to UTC regardless of the given zone.

💡 Hint

Think about how PostgreSQL treats timestamp types differently with AT TIME ZONE.

AT TIME ZONE converts timestamp without time zone by assuming it is in the given zone and returns timestamptz in UTC. For timestamptz, it converts UTC to local time in the given zone returning timestamp without time zone.