SQL AT TIME ZONE: Converting Stored UTC to a User's Local Time

AT TIME ZONE is a tool with two faces. What it does depends entirely on the type of its input: for a timestamptz it renders the moment as wall-clock time in the chosen zone, while for a naive timestamp it does the reverse, declaring that the value was already in that zone and returning the absolute moment.

Two modes, one operator

The rule is short: AT TIME ZONE always flips the type to the opposite one.

• timestamptz AT TIME ZONE 'zone' -> timestamp (wall time in that zone).
• timestamp AT TIME ZONE 'zone' -> timestamptz (interpret the naive value as local time in that zone).

-- timestamptz -> timestamp: what the clock on the wall in Moscow shows
SELECT TIMESTAMPTZ '2026-06-17 12:00:00+00' AT TIME ZONE 'Europe/Moscow';
-- 2026-06-17 15:00:00

-- timestamp -> timestamptz: this naive value WAS Moscow local time
SELECT TIMESTAMP '2026-06-17 15:00:00' AT TIME ZONE 'Europe/Moscow';
-- 2026-06-17 12:00:00+00

The two expressions are mirror images and round-trip cleanly. The first answers "what time is it in Moscow at this moment?"; the second answers "which absolute instant does 15:00 Moscow time correspond to?".

Converting stored UTC to a user's zone

The canonical pattern: you store created_at as timestamptz (which is UTC under the hood) and want to display it in a specific user's zone.

SELECT
  o.id,
  o.amount,
  o.created_at,                                          -- absolute moment (UTC)
  o.created_at AT TIME ZONE u.tz AS local_created_at     -- wall time for the user
FROM orders o
JOIN users u ON u.id = o.user_id;

Here u.tz is a text column holding a zone name such as 'America/Sao_Paulo'. Always use named IANA zones, never fixed offsets like '+03': a zone name knows the full history of DST transitions, a raw offset does not.

Bucketing orders by the user's "local day" is just as direct, convert first, then truncate:

SELECT
  DATE_TRUNC('day', o.created_at AT TIME ZONE u.tz) AS local_day,
  SUM(o.amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY 1
ORDER BY 1;

Daylight saving, where it actually bites

The main reason not to add offsets by hand is DST. Take New York around the spring-forward transition of 2026, when clocks jump from 02:00 straight to 03:00.

SELECT TIMESTAMPTZ '2026-03-08 06:30:00+00' AT TIME ZONE 'America/New_York' AS before_dst,
       TIMESTAMPTZ '2026-03-08 07:30:00+00' AT TIME ZONE 'America/New_York' AS after_dst;
-- before_dst = 2026-03-08 01:30:00   (offset -05)
-- after_dst  = 2026-03-08 03:30:00   (offset -04, the 02:00 hour never exists)

Exactly one hour of absolute time passed between the two moments, yet the wall clock jumped from 01:30 to 03:30, the 02:xx hour simply does not exist that night. A hardcoded -05 offset would give the wrong answer for the second row.

Gotchas

• Gotcha: mode confusion. Applying AT TIME ZONE to a value that is already a naive timestamp column does not "convert" it, it declares it was in that zone. Check the column's type first.
• Applying it twice round-trips. (ts AT TIME ZONE 'Europe/Moscow') yields a naive timestamp; apply AT TIME ZONE again and you get a timestamptz back, occasionally useful for "rezoning", but more often a bug.
• Use IANA names. Zones like 'Europe/Moscow' survive DST rule changes; offsets like '+03' do not.

MySQL and ClickHouse

Neither engine has AT TIME ZONE syntax. In MySQL use CONVERT_TZ(ts, 'UTC', 'Europe/Moscow') (the named zone tables must be loaded). In ClickHouse use toTimeZone(ts, 'Europe/Moscow') for a DateTime, remembering that the zone there is only a display attribute over a UTC-based timestamp.