How to Convert Time Zones in PostgreSQL

How do I convert a timestamp from UTC to a local time zone in PostgreSQL?

The AT TIME ZONE clause converts timestamp values from one time zone to another.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why use AT TIME ZONE for time-zone conversion?

AT TIME ZONE converts timestamp values reliably, letting you store UTC and display local times without manual math. It handles daylight-saving transitions automatically.

What is the basic syntax?

Use value AT TIME ZONE source_zone AT TIME ZONE target_zone. The first AT TIME ZONE turns a timestamp without time zone into timestamptz, the second converts it to another zone.

How do I convert UTC order times to the customer’s zone?

Join Orders with Customers, fetch each customer’s zone, and convert the stored UTC timestamp to that zone using AT TIME ZONE.

SELECT o.id, o.order_date AT TIME ZONE 'UTC' AT TIME ZONE c.user_tz AS local_time FROM Orders o JOIN Customers c ON c.id = o.customer_id;

Can I convert just the date portion?

Wrap the result in ::date to truncate the time portion without losing the zone-adjusted value.

How do I change the session time zone instead?

Run SET TIME ZONE 'America/Los_Angeles'; All subsequent timestamptz values render in that zone until reset.

Best practice: always store UTC

Store timestamptz in UTC to avoid daylight-saving bugs. Convert to local zones only when reading.

Best practice: index the raw timestamptz column

Create an index on the stored timestamptz column, not on computed zone conversions, for efficient range searches.

Common mistake: mixing timestamp types

timestamp without time zone lacks zone info. Casting it directly to another zone skips DST rules. Always attach the source zone first.

Common mistake: using textual offsets

'-05:00' ignores daylight savings. Use named zones such as 'America/New_York' to let PostgreSQL apply historical rules.

Need a quick reference?

Remember: value AT TIME ZONE source AT TIME ZONE target. Store UTC, convert on output, and use named zones.

Why How to Convert Time Zones in PostgreSQL is important

How to Convert Time Zones in PostgreSQL Example Usage


-- Show each customer's local order time and total amount
SELECT o.id,
       o.order_date AT TIME ZONE 'UTC' AT TIME ZONE c.user_tz AS customer_local_time,
       o.total_amount
FROM   Orders o
JOIN   Customers c ON c.id = o.customer_id
ORDER  BY customer_local_time DESC;

How to Convert Time Zones in PostgreSQL Syntax


-- Convert UTC order_date to America/New_York
SELECT order_date AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York'
FROM   Orders;

-- Attach zone to naive timestamp, then view in Europe/Paris
SELECT ('2024-06-01 10:00' AT TIME ZONE 'UTC') AT TIME ZONE 'Europe/Paris';

-- Set session zone for display
SET TIME ZONE 'Asia/Tokyo';

Common Mistakes

Using timestamp without time zone directly with a target zone. Fix: first attach the correct source zone with AT TIME ZONE, then convert.
Relying on numeric offsets like '+02' that ignore daylight saving shifts. Fix: always use named zones (e.g., 'Europe/Berlin').