Postgres Timezone Converter

AT TIME ZONE is the most misread operator in PostgreSQL: applied to a timestamp it returns a timestamptz, applied to a timestamptz it returns a timestamp, and the same expression can move a value forward or backward depending on which one you started with. This converter does the arithmetic for you. Enter a wall-clock time, choose the source and target IANA zones, and it shows the UTC instant, both offsets and abbreviations at that moment, whether the input falls into a daylight-saving gap or overlap, and the exact SQL: the double AT TIME ZONE idiom, a column rewrite, local-day grouping with date_trunc, and the SET timezone commands. Everything runs in your browser using its built-in time zone database; nothing is uploaded.

Input isTimestampColumn name

Source zone

Target zone

Conversion result

Source  Asia/Shanghai          2026-09-11 15:45:00  (UTC+08:00, GMT+8)
UTC     UTC                    2026-09-11 07:45:00  (epoch 1789112700)
Target  America/New_York       2026-09-11 03:45:00  (UTC-04:00, EDT)

Difference between zones at this instant: -12h.
Stored as timestamptz, PostgreSQL keeps the instant 2026-09-11 07:45:00+00:00 and displays it in the session TimeZone.
Day boundary: the source date is 2026-09-11, the target date is 2026-09-11 (same day).

Generated AT TIME ZONE SQL

-- 1. Convert a wall-clock literal from Asia/Shanghai to America/New_York
--    timestamp AT TIME ZONE zone -> timestamptz (interprets the value as Asia/Shanghai time)
--    timestamptz AT TIME ZONE zone -> timestamp (shows the instant as America/New_York wall time)
SELECT '2026-09-11 15:45:00'::timestamp AT TIME ZONE 'Asia/Shanghai' AT TIME ZONE 'America/New_York' AS local_in_target;
-- Expected: 2026-09-11 03:45:00

-- 2. Same conversion for a timestamp WITHOUT time zone column that stores Asia/Shanghai wall time
SELECT created_at,
       created_at AT TIME ZONE 'Asia/Shanghai'                        AS as_instant,   -- timestamptz
       created_at AT TIME ZONE 'Asia/Shanghai' AT TIME ZONE 'America/New_York' AS in_target     -- timestamp
FROM events;

-- 3. Fix a column that was wrongly created as timestamp: convert it to timestamptz in place
ALTER TABLE events
  ALTER COLUMN created_at TYPE timestamptz
  USING created_at AT TIME ZONE 'Asia/Shanghai';

Session time zone commands

-- Check and change the session time zone (affects how timestamptz is displayed and how literals without offset are read)
SHOW timezone;
SET timezone = 'America/New_York';                 -- this session only
SET LOCAL timezone = 'America/New_York';           -- current transaction only
ALTER DATABASE mydb SET timezone = 'America/New_York';   -- default for new connections to mydb
ALTER ROLE app_user SET timezone = 'America/New_York';   -- default for one role

-- Verify what PostgreSQL knows about both zones right now
SELECT name, abbrev, utc_offset, is_dst FROM pg_timezone_names WHERE name IN ('Asia/Shanghai', 'America/New_York');

-- Avoid abbreviations such as 'EST' or 'CST' in AT TIME ZONE: they are fixed offsets and ignore daylight saving.

Do more than postgres timezone converter — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Type the timestamp as YYYY-MM-DD HH:MI:SS and pick the zone it was recorded in, then the zone you want to see it in. Choose Custom to type any IANA name.
Read the conversion: source, UTC and target wall times with offsets, plus a warning if the time does not exist or happens twice because of daylight saving.
Copy the generated AT TIME ZONE SQL for a literal or a column, the date_trunc grouping query, and the SET timezone commands into your editor.

Frequently asked questions

What is the difference between timestamp AT TIME ZONE and timestamptz AT TIME ZONE?

They are opposite operations that share one keyword. timestamp (without time zone) AT TIME ZONE 'Europe/Berlin' says: treat this wall-clock value as Berlin time and give me the absolute instant, so the result is timestamptz. timestamptz AT TIME ZONE 'Europe/Berlin' says: take this absolute instant and show me the Berlin wall-clock time, so the result is timestamp without time zone. Chaining the two, value AT TIME ZONE 'source' AT TIME ZONE 'target', converts a wall-clock time from one zone to another, which is exactly what the generated SQL does.

Should I use 'EST' or 'America/New_York' in PostgreSQL?

Use the IANA region name. 'America/New_York' switches between EST (UTC-5) and EDT (UTC-4) automatically, while the abbreviation 'EST' is a fixed offset that gives wrong answers for half of the year. The same applies to 'CST', 'PST' and friends; 'CST' is even ambiguous between China, Cuba and US Central time. PostgreSQL lists every accepted name in the pg_timezone_names view, and the abbreviations it knows in pg_timezone_abbrevs. Note also that POSIX-style strings such as 'UTC+3' have the sign inverted compared to ISO offsets, which is another reason to stick to region names.

How do I group events by local day for users in different time zones?

Store the event time as timestamptz, keep each user's IANA zone in a column, and convert at query time: date_trunc('day', e.created_at AT TIME ZONE u.time_zone). Because the conversion result is a plain timestamp, the grouping key is the local calendar day. On PostgreSQL 16 or newer you can write date_trunc('day', e.created_at, u.time_zone) and keep a timestamptz result. For a single zone, prefer the range filter the tool generates, which converts the boundaries instead of every row and can use an index on created_at. Chat2DB can run these queries against your database and lets you set the session time zone per connection: download it at https://chat2db.ai/download or open https://app.chat2db.ai.