Time zones and AT TIME ZONE in PostgreSQL

Introduction

Time zones help us understand what time it is in different parts of the world. The AT TIME ZONE command changes the time to a specific time zone so we can see it correctly.

You want to show the time of an event to people in different countries.

You have a meeting scheduled in UTC but want to see it in your local time.

You store times without time zone but want to convert them to a specific zone.

You want to compare times from different zones in a common format.

Syntax

PostgreSQL

timestamp_value AT TIME ZONE zone_name

timestamp_value can be a timestamp with or without time zone.

zone_name is a string like 'UTC', 'America/New_York', or an offset like '+02'.

Examples

Converts a timestamp without time zone assuming it is in local time to UTC timestamp with time zone.

PostgreSQL

TIMESTAMP '2024-06-01 12:00:00' AT TIME ZONE 'UTC'

Converts a timestamp with time zone from UTC to New York time (returns timestamp without time zone).

PostgreSQL

TIMESTAMP WITH TIME ZONE '2024-06-01 12:00:00+00' AT TIME ZONE 'America/New_York'

Converts a timestamp without time zone assuming it is in +02 offset to UTC timestamp with time zone.

PostgreSQL

TIMESTAMP '2024-06-01 12:00:00' AT TIME ZONE '+02'

Sample Program

This query shows how to convert a timestamp without time zone to UTC and New York time (both as timestamp with time zone), and how to convert a timestamp with time zone from UTC to New York time (returns timestamp without time zone).

PostgreSQL

SELECT
  TIMESTAMP '2024-06-01 12:00:00' AT TIME ZONE 'UTC' AS utc_time,
  TIMESTAMP '2024-06-01 12:00:00' AT TIME ZONE 'America/New_York' AS ny_time,
  TIMESTAMP WITH TIME ZONE '2024-06-01 12:00:00+00' AT TIME ZONE 'America/New_York' AS ny_time_from_tz;

Important Notes

When you use AT TIME ZONE on a timestamp without time zone, it treats the timestamp as if it is in the given zone and converts it to a timestamp with time zone in UTC.

When you use AT TIME ZONE on a timestamp with time zone, it converts it to the given zone but returns a timestamp without time zone.

Time zones like 'America/New_York' adjust for daylight saving time automatically.

Summary

Time zones help show correct local times worldwide.

AT TIME ZONE converts timestamps between zones.

Use it to display or compare times correctly across regions.