Convert a Timestamp Between Time Zones in SQL (per Dialect)

Turn a stored UTC timestamp into wall-clock time in another zone (and back).

UTC → America/New_York

-- MySQL
SELECT CONVERT_TZ(ts, 'UTC', 'America/New_York');

-- PostgreSQL  (read a UTC timestamp AS UTC, then view in the target zone)
SELECT ts AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York';

-- Snowflake
SELECT CONVERT_TIMEZONE('UTC', 'America/New_York', ts);

-- DuckDB  (needs the icu extension for named zones)
SELECT (ts AT TIME ZONE 'UTC') AT TIME ZONE 'America/New_York';

-- BigQuery  (format a UTC TIMESTAMP in a zone)
SELECT FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', ts, 'America/New_York');
-- or as a civil datetime:
SELECT DATETIME(ts, 'America/New_York');

SQLite (no time-zone database)

SQLite only knows UTC and the server’s local zone:

SELECT datetime(ts, 'localtime');          -- UTC stored value -> server local

Gotchas: MySQL CONVERT_TZ returns NULL unless the zoneinfo tables are loaded (mysql_tzinfo_to_sql); without them, only fixed offsets like '+00:00' work. The Postgres AT TIME ZONE operator is directional and subtle: applied to a timestamp (no zone) it interprets it in that zone; applied to a timestamptz it renders it in that zone — chaining two (AT TIME ZONE 'UTC' AT TIME ZONE 'NY') does the UTC→NY conversion. SQLite has no IANA zone database — it can only do 'localtime'/'utc', so do named-zone math in the app layer. Named zones handle DST automatically; fixed offsets do not.