Semantic inconsistency between IANA names and fixed offsets in TIMESTAMP WITH TIME ZONE

Short answer

In Firebird 4.0+, a stored fixed offset is a literal displacement, not a reference to a region. When the server's ICU database is updated, IANA-named values are re-evaluated at read time against the new DST rules, but fixed-offset values are not — the engine keeps applying the stored offset exactly as written. There is no SQL statement, engine setting, or configuration option that forces re-evaluation of a fixed offset against regional rules. The metadata type (zone ID vs. offset) is immutable once written. The only way to change semantics is to rewrite the data, as described below.

How the two metadata types diverge

Firebird stores TIMESTAMP WITH TIME ZONEEurope/Berlin, read-time conversion consults the server's current ICU database, so an ICU update that changes DST rules immediately affects subsequent conversions of IANA-sourced values. With a fixed offset like +02:00, the offset is frozen at insert time; ICU updates do not touch it. A literal such as TIMESTAMP '2024-06-15 12:00:00+02:00' produces a fixed-offset value, not an IANA-aware one.

Assumption: this behavior applies to Firebird 4.0 and later, where TIMESTAMP WITH TIME ZONE was introduced. The engine's ICU version is tied to the server installation — bundled on Windows, typically the system ICU on Linux — and an ICU upgrade requires a server restart. The engine itself does not expose a switch to reinterpret offsets; the only lever is the data itself.

What you can do

To apply regional rules to existing fixed-offset rows, you must migrate the data: read the UTC instant, determine the desired IANA zone, and rewrite the column using AT TIME ZONE or an explicit CAST with the zone name. There is no built-in re-evaluation mechanism. Backup/restore (gbak) preserves the original metadata type; it does not convert fixed offsets to IANA zones. Likewise, replication or ETL pipelines that strip timezone metadata and re-apply a fixed offset will lose IANA semantics permanently.

To verify the divergence: create a test table with two columns, one inserted via CAST('2024-06-15 12:00:00 Europe/Berlin' AS TIMESTAMP WITH TIME ZONE), another via CAST('2024-06-15 12:00:00+02:00' AS TIMESTAMP WITH TIME ZONE). Query both with CAST(col AT TIME ZONE 'Europe/Berlin' AS TIMESTAMP). Initially both columns should show identical output. Then update the server's ICU data (e.g., apt upgrade icu on Linux, or replace icudt*.dll on Windows) and restart Firebird. Re-run the same conversion query; the IANA-sourced column may show a different local time if DST rules changed, while the fixed-offset column remains unchanged.

One caveat: mixing IANA and fixed-offset inserts for the same logical timezone creates silent divergence after ICU updates or DST policy changes. If your application constructs literals with explicit offsets, it produces fixed-offset values, not IANA-aware ones. To avoid the divergence, always insert IANA zone names for regions with DST rules, and reserve fixed offsets for cases where you truly mean a literal displacement.