Time zones and AT TIME ZONE in PostgreSQL - Mini Project: Build & Apply

Working with Time Zones using AT TIME ZONE in PostgreSQL

📖 Scenario: You are managing an international event scheduling database. Events are stored with timestamps in UTC, but users want to see event times in their local time zones.

🎯 Goal: Build a simple PostgreSQL query that converts UTC timestamps to a specified local time zone using the AT TIME ZONE clause.

📋 What You'll Learn

Create a table called events with columns event_id (integer) and event_time_utc (timestamp without time zone).

Insert three events with exact UTC timestamps: '2024-06-01 12:00:00', '2024-06-01 18:30:00', and '2024-06-02 09:15:00'.

Create a variable or CTE named target_zone with the value 'America/New_York'.

Write a SELECT query that converts event_time_utc to the target_zone time zone using AT TIME ZONE and returns event_id and the converted time as event_time_local.

💡 Why This Matters

🌍 Real World

Event scheduling systems often store times in UTC but need to display them in users' local time zones for clarity.

💼 Career

Understanding time zone conversions is essential for database developers and analysts working with global data.

1

Create the events table and insert UTC timestamps

Create a table called events with columns event_id as integer and event_time_utc as timestamp without time zone. Insert three rows with event_id values 1, 2, and 3 and event_time_utc values '2024-06-01 12:00:00', '2024-06-01 18:30:00', and '2024-06-02 09:15:00' respectively.

PostgreSQL

-- Create the events table and insert the three UTC timestamps
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows with exact timestamps.

2

Define the target time zone variable

Create a Common Table Expression (CTE) named target_zone that contains a single column zone with the value 'America/New_York'.

PostgreSQL

CREATE TABLE events (
    event_id INTEGER,
    event_time_utc TIMESTAMP WITHOUT TIME ZONE
);

INSERT INTO events (event_id, event_time_utc) VALUES
(1, '2024-06-01 12:00:00'),
(2, '2024-06-01 18:30:00'),
(3, '2024-06-02 09:15:00');

-- Create a CTE named target_zone with column zone = 'America/New_York'
-- Your code here

Need a hint?

Use a CTE with WITH target_zone AS (SELECT 'America/New_York'::text AS zone).

3

Write the query to convert UTC to local time

Using the target_zone CTE and the events table, write a SELECT query that returns event_id and event_time_utc AT TIME ZONE 'UTC' AT TIME ZONE zone as event_time_local. Join events with target_zone without any join condition.

PostgreSQL

CREATE TABLE events (
    event_id INTEGER,
    event_time_utc TIMESTAMP WITHOUT TIME ZONE
);

INSERT INTO events (event_id, event_time_utc) VALUES
(1, '2024-06-01 12:00:00'),
(2, '2024-06-01 18:30:00'),
(3, '2024-06-02 09:15:00');

WITH target_zone AS (
    SELECT 'America/New_York'::text AS zone
)
-- Write SELECT query joining events and target_zone to convert event_time_utc to event_time_local
-- Your code here

Need a hint?

Use AT TIME ZONE 'UTC' AT TIME ZONE t.zone to convert UTC timestamp to local time.

4

Complete the query with ORDER BY

Add an ORDER BY event_time_local clause at the end of the SELECT query to sort events by their local time.

PostgreSQL

CREATE TABLE events (
    event_id INTEGER,
    event_time_utc TIMESTAMP WITHOUT TIME ZONE
);

INSERT INTO events (event_id, event_time_utc) VALUES
(1, '2024-06-01 12:00:00'),
(2, '2024-06-01 18:30:00'),
(3, '2024-06-02 09:15:00');

WITH target_zone AS (
    SELECT 'America/New_York'::text AS zone
)
SELECT
    e.event_id,
    e.event_time_utc AT TIME ZONE 'UTC' AT TIME ZONE t.zone AS event_time_local
FROM events e, target_zone t
-- Add ORDER BY event_time_local here
-- Your code here

Need a hint?

Use ORDER BY event_time_local to sort the results by local event time.