Time zones and AT TIME ZONE in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to convert the current timestamp to UTC time zone.

PostgreSQL

SELECT CURRENT_TIMESTAMP [1] 'UTC';

Drag options to blanks, or click blank then click option'

AAT TIME ZONE

BNOW()

CTO_TIMESTAMP

DEXTRACT

Attempts:

3 left

💡 Hint

Common Mistakes

Using NOW() instead of AT TIME ZONE.

Trying to use EXTRACT for time zone conversion.

The AT TIME ZONE clause converts a timestamp to the specified time zone.

2fill in blank

medium

Complete the code to convert a timestamp with time zone to 'America/New_York' time zone.

PostgreSQL

SELECT timestamp_with_time_zone_column [1] 'America/New_York' FROM events;

Drag options to blanks, or click blank then click option'

ADATE_TRUNC

BAT TIME ZONE

CEXTRACT

DTO_CHAR

Attempts:

3 left

💡 Hint

Common Mistakes

Using TO_CHAR which formats but does not convert time zones.

Using EXTRACT which extracts parts of the timestamp.

AT TIME ZONE converts the timestamp to the specified time zone.

3fill in blank

hard

Fix the error in the code to convert '2024-06-01 12:00:00' timestamp to 'Europe/London' time zone.

PostgreSQL

SELECT TIMESTAMP '2024-06-01 12:00:00' [1] 'Europe/London';

Drag options to blanks, or click blank then click option'

ACONVERT_TZ

BTO_TIMESTAMP

CAT TIME ZONE

DSET TIMEZONE

Attempts:

3 left

💡 Hint

Common Mistakes

Using CONVERT_TZ which is MySQL syntax.

Using SET TIMEZONE which sets session time zone but does not convert timestamp.

AT TIME ZONE is the correct syntax to convert timestamp to a time zone in PostgreSQL.

4fill in blank

hard

Fill both blanks to convert a timestamp without time zone to UTC and then to 'Asia/Tokyo'.

PostgreSQL

SELECT (timestamp_column [1] 'UTC') [2] 'Asia/Tokyo' FROM meetings;

Drag options to blanks, or click blank then click option'

AAT TIME ZONE

BTO_TIMESTAMP

CNOW()

DEXTRACT

Attempts:

3 left

💡 Hint

Common Mistakes

Using TO_TIMESTAMP which parses strings but does not convert time zones.

Using NOW() which returns current time, not conversion.

First, convert the timestamp without time zone to UTC using AT TIME ZONE 'UTC', then convert that result to 'Asia/Tokyo' time zone again using AT TIME ZONE 'Asia/Tokyo'.

5fill in blank

hard

Fill all three blanks to convert 'event_time' from 'UTC' to 'Europe/Paris' and extract the hour.

PostgreSQL

SELECT EXTRACT(HOUR FROM (event_time [1] [2]) [3] 'Europe/Paris') AS paris_hour FROM schedule;

Drag options to blanks, or click blank then click option'

AAT TIME ZONE

B'UTC'

D'Europe/Paris'

Attempts:

3 left

💡 Hint

Common Mistakes

Using only one AT TIME ZONE clause.

Not quoting the time zone names.

First, convert event_time from 'UTC' using AT TIME ZONE 'UTC', then convert that timestamp to 'Europe/Paris' using AT TIME ZONE 'Europe/Paris', finally extract the hour.