How to Convert Timezones in PostgreSQL

How do I convert UTC timestamps to local time in PostgreSQL?

AT TIME ZONE converts a timestamp between time zones without altering the stored value.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is the quickest way to change a timestamp’s time zone?

Use the AT TIME ZONE operator. It re-interprets a timestamp as belonging to one zone and immediately converts it to another, returning either timestamp with time zone or timestamp without time zone depending on input type.

How does AT TIME ZONE work with ecommerce data?

Store all order times in UTC, then convert when displaying to shoppers. Example: convert Orders.order_date from UTC to the customer’s locale during SELECT, keeping the table in UTC for consistency.

Which syntax options exist for time zone conversion?

Three common patterns:

timestamp_col AT TIME ZONE 'Zone'
timezone('Zone', timestamp_col)
SET TIME ZONE 'Zone'; SELECT ... (session-wide)

Each covers single-value conversion, functional style, and session default.

How do I convert UTC order dates to a customer’s zone?

Join customer profile data, then apply AT TIME ZONE. Example below shows converting to America/Chicago using a subquery.

What about daylight-saving changes?

PostgreSQL’s time-zone database tracks DST automatically. Always use timestamp with time zone plus IANA names like 'Europe/Berlin' to stay DST-safe.

When should I use SET TIME ZONE?

Use it in analytics scripts or app connections when every query in the session should share the same display zone. It alters only the returned text, not stored values.

Best practices for production systems?

Store in UTC, convert at the edge; use explicit column types; cache user zones; test queries around DST boundaries; avoid hard-coding abbreviations like PST.

Why How to Convert Timezones in PostgreSQL is important

How to Convert Timezones in PostgreSQL Example Usage


SELECT
    o.id,
    c.name AS customer_name,
    -- Convert order_date stored in UTC to the customer’s zone
    (o.order_date AT TIME ZONE 'UTC' AT TIME ZONE c.preferred_tz) AS local_order_time
FROM Orders o
JOIN Customers c ON c.id = o.customer_id
WHERE o.order_date >= '2024-01-01';

How to Convert Timezones in PostgreSQL Syntax


-- Reinterpret UTC timestamp as local time, return timestamp without tz
SELECT order_date AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' AS order_date_est
FROM Orders;

-- Functional equivalent
SELECT timezone('America/Los_Angeles', order_date) AS order_date_pst
FROM Orders;

-- Session-wide default display zone
SET TIME ZONE 'Asia/Tokyo';
SELECT id, order_date FROM Orders;

Common Mistakes

Mistake: Casting a <code>timestamp without time zone</code> to a different zone directly. Why it’s wrong: The timestamp lacks context, so PostgreSQL assumes it is already local and won’t adjust the value. Fix: First attach the correct source zone with <code>AT TIME ZONE</code>, then convert.
Mistake: Storing mixed zones in the same column. Why it’s wrong: Queries cannot safely compare or aggregate inconsistent data. Fix: Standardize on UTC for storage; use conversion only when selecting.