Shape / columns · All operations

Convert between time zones

A variant of Parse a date or time

Attach, change or convert the time zone of a timestamp.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Attach a time zone and convert between zones

SQL
CREATE TEMP TABLE result AS
SELECT TIMESTAMPTZ '2026-02-10 00:00:00+00' AT TIME ZONE 'Australia/Sydney' AS sydney_clock,
       TIMESTAMP '2026-02-10 09:00:00' AT TIME ZONE 'Australia/Sydney' AS sydney_instant,
       TIMESTAMPTZ '2026-07-01 12:00:00+00' AT TIME ZONE 'Europe/London' AS london_clock;
-- an instant is printed as epoch seconds, so the recorded output does not depend on the machine's time zone
SELECT sydney_clock, epoch(sydney_instant) AS sydney_instant_epoch, london_clock FROM result;
SELECT CASE WHEN ((SELECT sydney_clock = TIMESTAMP '2026-02-10 11:00:00' AND epoch(sydney_instant) = epoch(TIMESTAMPTZ '2026-02-09 22:00:00+00') AND london_clock = TIMESTAMP '2026-07-01 13:00:00' FROM result)) THEN 'as expected' ELSE error('Sydney was not 11 hours ahead of UTC in February or London one hour ahead in July') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “Sydney was not 11 hours ahead of UTC in February or London one hour ahead in July”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

DuckDB (1)
Duckle (1)
  • Timezone Convertread 2026-09-25

    convert a timestamp between time zones with AT TIME ZONE

KNIME Analytics Platform (1)
  • Time Zone Modifierread 2026-09-27

    Add, change, shift or remove the time zone of date&time columns

Power Query M (1)
  • DateTimeZone functionsread 2026-09-27

    16 functions: DateTimeZone.SwitchZone, DateTimeZone.ToUtc, DateTimeZone.ToLocal, DateTimeZone.RemoveZone, DateTimeZone.UtcNow

Background

In PostgreSQL, AT TIME ZONE converts a timestamp without time zone to one with time zone, assuming the value is in the named zone.[1] In PostgreSQL, AT TIME ZONE also converts a timestamp with time zone to one without, as the time would appear in the named zone.[1]

When to use it

Time zone abbreviations are ambiguous in practice: CST means one thing in China and something else in North America, and IST can refer to time in India, Ireland or Israel.[2]

DuckDB's AT TIME ZONE does not accept a numeric time zone such as '0200' and raises an error.[3]

The method

The tz database is a collaborative compilation of information about the world's time zones and daylight saving rules, also known as the IANA time zone database after the Internet Assigned Numbers Authority (IANA).[4] The tz database's founding contributor was Arthur David Olson, and Paul Eggert has been its editor and maintainer since 2005.[4] In the tz database, a timezone is a region where local clocks have all agreed since 1970, and it may use several offsets from UTC, such as for standard and daylight saving time.[4] Timezone names such as America/New_York and Europe/Paris follow a uniform naming convention designed by Paul Eggert.[4] The IANA Time Zone Database is updated periodically to reflect changes made by political bodies to time zone boundaries, UTC offsets and daylight-saving rules.[5]

Compared with the alternatives

In Python, an aware datetime represents a specific moment that is not open to interpretation, while whether a naive one represents UTC, local time or another zone is purely up to the program.[6]

Sources

  1. [1]PostgreSQL: Documentation: 18: 9.9. Date/Time Functions and Operatorsdocumentation
  2. [2]Theory and pragmatics of the tz code and datadocumentation
  3. [3]Timestamp with Time Zone Functions – DuckDBdocumentation
  4. [4]tz database - Wikipediasecondary source
  5. [5]Time Zonesorganisation
  6. [6]datetime — Basic date and time types — Python 3.15.2 documentationdocumentation