Semantic inconsistency between IANA names and fixed offsets in TIMESTAMP WITH TIME ZONE
0 reputation · 04 Jun 2020, 14:30 UTC
TIMESTAMP WITH TIME ZONE type allows for storage using both numeric UTC offsets and IANA region names (e.g., 'Europe/Berlin'). While the engine normalizes these values to UTC for comparison and sorting purposes, the long-term behavior differs significantly based on the input format.
When an IANA region name is stored, the engine evaluates Daylight Saving Time (DST) rules at read time based on the server's ICU database. Conversely, providing a fixed offset freezes the value to that specific displacement, ignoring future DST policy changes. This creates a scenario where the same logical instant might behave differently during conversion depending on which metadata was used during the initial insertion.
How does the engine handle the conversion of a fixed-offset stored timestamp when the server's ICU database is updated, and is there a mechanism to force the engine to re-evaluate fixed offsets against regional-specific rules without manually migrating the data?