Oninit® UDR — tzdte: Time zone data

Where the rules come from, how generations work, how to install a new release, and how to find and repair values after a rule change.

Where the rules live

Zone rules exist in exactly one place: tables in each database, loaded from bundles Oninit builds from the IANA time zone database. The operating system's zone files and the server's TZ are never consulted, so the same SQL gives the same answer on Linux and AIX, on a primary and on every secondary, and in a restored archive.

Generations

Each released IANA ruleset is a generation with an id Oninit assigns. The id is stored inside every value, next to the offset that was resolved when it was written, so a value can always be traced to the rules that produced it.

Installing a new release

1. Obtain the new bundle: tzdata_<version>.unl, tzdata_<version>.gen and the manifest. Check each file against unl_sha256 and gen_sha256 in the manifest.
2. Link the files as tzdata.unl and tzdata.gen and run tzdte_load_tzdata.sql on each database. The load is one transaction: it validates every row and name, inserts the names, the generation and its rules, verifies the generation checksum, and moves the current marker last. Any failure rolls everything back and leaves the previous generation current.
3. Check it: EXECUTE FUNCTION tzdte_generation_checksum(<gen>) should report PASS.
4. Existing values are untouched and read exactly as before. Find the ones the new rules move (below) and recalculate or rebase them if you want.

New sessions pick up the new generation through sysdbopen(). tzdte_current_generation() and tzdte_tzdata_version() show each database's current generation. A change to the rows after loading is caught by tzdte_generation_checksum; the in-memory cache is built from verified data and is rebuilt on the next reload.

Indexes that depend on rules

Functions that read only what a value stores give the same answer after any load, so they are safe in functional indexes, check constraints and fragmentation expressions: tzdte_utc, tzdte_local(t), tzdte_offset(t), tzdte_zone, tzdte_stored_generation, tzdte_iso(t) and tzdte_text(t).

Functions that make a new conversion under the current generation (tzdte_at, tzdte_from_local, tzdte_add_days, tzdte_add_months, tzdte_trunc_day, tzdte_rebase) and anything called with RecalculateOffset, tzdte_recalc or tzdte_rules_changed can change when the current generation does. Do not index them, or rebuild such an index after each load. Expressions that compare only instants are unaffected.

Finding and repairing affected values

Suppose a meeting at 09:00 London in 2027 is stored, and the UK then changes its daylight-time rules and a new generation is installed. The row does not change: it still reads 09:00 at its stored offset, and the instant is the same. What changed is what the rules now say for that wall-clock time. That is right for logs and audit records and wrong for schedules. You decide, per query, what to do:

-- which rows does the new generation move?
SELECT COUNT(*) FROM appointments WHERE tzdte_rules_changed(starts_at);

-- show the same instant under the current rules; the row is not touched
SELECT tzdte_text(starts_at, 'RecalculateOffset') FROM appointments;

-- for a schedule: keep the wall-clock promise (a new instant, a new generation)
UPDATE appointments
   SET starts_at = tzdte_rebase(starts_at)
 WHERE tzdte_rules_changed(starts_at);

Schedulers should rebase after each load, or store a local DATETIME plus a zone name instead. No rule change ever rewrites a row by itself.

Unload and load

UNLOAD writes the local time at the stored offset and, for named zones, the generation (… America/Chicago #1). LOAD rebuilds the identical instant, zone, offset and generation, so a dump taken before a tzdata update reloads cleanly whatever the rules are now. If that generation is not installed in the target database, the offset is accepted as the value's provenance because it cannot be checked. Text without a # generation is a new conversion: its offset must agree with the zone's rules in the current generation, or it is an error.

dbexport and dbimport use the same text, so the generation is kept and the rules tables travel with the data. A plain dbimport of the export stops with error 201, because dbexport writes the two DATETIME casts without their qualifier; import with tools/tzdte_dbimport.sh, which repairs them (see the Install page). Afterwards check tzdte_generation_checksum(<gen>) for every generation.