Does DuckDB convert TIMESTAMP WITH TIME ZONE to UTC on insert, or ignore the zone?

Answer to the question

DuckDB always converts a TIMESTAMP WITH TIME ZONE literal to UTC at the moment of insertion. The time‑zone offset is applied, then the zone component is discarded, leaving a plain TIMESTAMP stored in UTC. AT TIME ZONE subsequently interprets this UTC instant and shifts it to the requested zone.

How to verify the conversion

-- Create a table that uses the type
CREATE TABLE t (ts TIMESTAMP WITH TIME ZONE);

-- Insert a value with a known offset
INSERT INTO t VALUES ('2023-01-01 12:00:00+02');

-- Inspect the raw stored value – should be UTC
SELECT ts FROM t; -- 2023-01-01 10:00:00

-- Apply AT TIME ZONE to shift back to a local zone
SELECT ts AT TIME ZONE 'Europe/Paris' FROM t; -- 2023-01-01 11:00:00

-- Check the DuckDB version
SELECT version();

What AT TIME ZONE does

The function takes the stored UTC instant and produces a wall‑clock time in the target zone, using the same rules that the database applies during insertion. If the stored value had been left unchanged, the conversion would be incorrect.

Version notes

Since the 0.3 series, the conversion-to‑UTC semantics have been stable. No release notes for 0.9+ or later mention a change that would ignore the offset. If you are running a very early 0.1/0.2 build, run the test above to confirm the behavior.

Caveats

Drivers (e.g., JDBC, Python, R) may re‑apply offsets when reading data, depending on how they interpret the type. Verify the client behavior if you observe discrepancies.

Missing diagnostic detail

If you are using a driver or an older DuckDB binary, please share the exact SELECT version() output. The behavior may differ in pre‑0.3 releases.