Oninit® UDR — tzdte: Design decisions
What tzdte does, the alternatives that were weighed,
and why each choice was made — so you can predict its behaviour and know
what it will and will not do.
A time-zone-aware type is mostly policy. The C code that converts an instant to local time is the easy part; the difficult part is deciding what equality means, what a missing local time turns into, where the rules come from, and what happens when a government changes them. Each section below gives the decision, the reason, and what follows from it.
Values and comparison
| Topic | Decision | Why, and what follows |
|---|---|---|
| Equality | Comparison, B-tree order and = use the UTC instant only. The zone is metadata. | It matches PostgreSQL and Oracle, gives the simplest index operator class, and needs no zone data to compare. The alternative, instant first and zone as a tiebreak, gives a total order but makes 10:00 America/Chicago = 15:00 UTC false, which surprises everyone. Consequences: DISTINCT keeps an arbitrary zone among equal instants; GROUP BY on the column does too when an index on the column supplies the order, but otherwise fails, as does COUNT(DISTINCT col), with error 9643 (the type cannot be hashed), so group or count over a derived value such as tzdte_utc(col) instead; and a unique index rejects a second value for the same instant. |
| Hashing | The type is registered CANNOTHASH. | Informix's default byte-wise hash would treat equal instants in different zones as different values. Large joins use sort-merge or indexed nested loops, so comparison is kept cheap: it never touches zone data. |
| Text output | The stored offset and the zone name, nothing looked up: YYYY-MM-DD HH:MM:SS.fffff±HH:MM[:SS] Zone. Default retrieval is stored UTC plus the stored offset, so it needs no rules and is unaffected by a later rule change, a renamed zone or a missing generation. RecalculateOffset presents the same instant under IANA rules on request. | The offset disambiguates the repeated hour at the end of daylight time, so output parses back to the same instant. Seconds appear in the offset only when needed (old local mean time). Fixed-offset values print no zone name. The export form used by UNLOAD adds #<generation>, so loading restores the identical value. tzdte_iso() gives ISO 8601 without the zone name for clients that need it. |
| Text input | An offset, a zone, or both are required. Bare local text is rejected. | Assuming UTC or server time silently is how zone bugs start. |
| Offset and zone that disagree | An error: the offset must equal the zone's offset in the value's generation (the current one, or the one named by a trailing #<gen>). | Inconsistent input is not guessed at. An exported value carries its generation, so it still reloads after the rules have changed; if that generation is not installed the offset is accepted as provenance. Until 5 Oct 2026 a disagreeing offset was accepted and flagged. |
| Zone names | IANA names, matched exactly (case-sensitive; america/chicago is an error); UTC, Z, GMT; ISO offsets (±HH, ±HH:MM, ±HH:MM:SS, ±HHMM). | Abbreviations (CST, IST, BST) are rejected because each has several meanings; CST is Central US, China and Cuba. Legacy IANA names such as EST5EDT still resolve, as names. Raw POSIX rule strings are not accepted as input. A bare ISO offset is east-positive; the inverted signs of POSIX strings and Etc/GMT±N only ever appear inside IANA data. Names are at most 64 characters, and two names that differ only in case are refused. No spelling repair, no similar-name substitution, no operating-system fallback and no silent UTC. |
| Range and precision | The UTC instant and the local time must both lie within 0001-01-01 and 9999-12-31. Resolution is 10 microseconds, the same as DATETIME YEAR TO FRACTION(5). | A cast to or from DATETIME never overflows and loses nothing. Text input takes one to five fraction digits; a sixth digit, even a 0, is an error (tzdte: fraction has more than 5 digits; resolution is 10 microseconds), and output shows five digits. A stored value is always a multiple of 10 microseconds. Leap seconds are ignored, as in POSIX time; zone files that carry leap-second records are rejected. |
Resolving a local time
The principle is if in doubt, fail: a local time that does not exist or occurs twice is an error unless the caller asks for a correction.
| Topic | Decision | Why, and what follows |
|---|---|---|
| Default policy | An error for a time in a spring-forward gap or a fall-back overlap. Text input carries no flags, so give an offset ('2026-11-01 01:30-06:00 America/Chicago') or use tzdte_from_local(dt, zone, 'AutoCorrectInsert'). | Guessing hides data problems. Calendar results (tzdte_add_days, tzdte_add_months, tzdte_trunc_day, tzdte_rebase) follow the same rules, controlled by AutoCorrectRetrieve. Until 5 Oct 2026 the default was the later candidate, as in PostgreSQL. |
| AutoCorrect | Opt-in, per call. A gap moves forward by the real gap, using the offset before the transition (02:30 becomes 03:30 in a one-hour gap; 02:10 becomes 02:40 in Lord Howe's 30-minute gap). An overlap takes the earliest instant. | It never corrects zone names, guesses missing zone information, selects arbitrary generations or hides corruption. A correction that cannot be made deterministically is still an error. |
| Day boundaries | tzdte_trunc_day returns local midnight, or the first instant of the day where midnight does not exist. | Havana skips midnight on the change day, so “midnight” alone is not a safe answer. |
Arithmetic and conversion
| Topic | Decision | Why, and what follows |
|---|---|---|
| Interval addition | + and - with a day-time interval are exact elapsed time. | A day on a daylight-time change is 23 or 25 hours, and both readings of “plus one day” are legitimate: SLAs and monitoring want elapsed time, schedules want wall-clock time. Informix INTERVAL DAY TO FRACTION holds a single elapsed quantity, and INTERVAL(1) DAY TO DAY and INTERVAL(24) HOUR TO HOUR are the same value, so the qualifier cannot carry the distinction; using it as a hint would make results depend on casts and expression shape. Exact addition matches Oracle and the SQL standard, matches existing DATETIME behaviour so migrated SQL keeps its meaning, and keeps (t + d) - t = d true. |
| Calendar arithmetic | Named functions: tzdte_add_days keeps the wall-clock time; tzdte_add_months clamps to the month end. | The two meanings of “a day” get two different spellings, so nothing depends on how an expression happens to be written. |
| Year-to-month intervals | Not accepted by + and -. Use tzdte_add_months. | Informix ignores an interval's qualifier when it matches a routine, so only one plus and one minus can be registered, and they take day-time intervals; a YEAR TO MONTH interval is rejected with error 1266. Months vary in length, so calendar months get their own function, which clamps to the month end. |
| Subtraction | tzdte - tzdte returns INTERVAL DAY(9) TO FRACTION(5). | Exact: both operands are multiples of 10 microseconds, the finest an Informix interval holds. |
Casts from DATETIME | Explicit only, and the DATETIME is taken as UTC. | An implicit cast would silently treat CURRENT, which is server-local time, as UTC. The digits are read directly, so DBTIME and GL_DATETIME cannot affect the result. Other conversions are named functions: tzdte_from_utc, tzdte_from_local, tzdte_utc, tzdte_local. |
| The current time | tzdte_now() reads the wall clock once per statement, like CURRENT. The zone argument defaults to UTC. | It cannot be built on CURRENT, which follows the server's TZ. For measuring durations, use a monotonic clock in your own code: the wall clock can step back under NTP. |
Flags
Functions where it applies take one optional extra argument, an
INTEGER bitmap or comma-separated exact names with no spaces.
Omitted, 0 and '' are the defaults.
| Bit | Name | Effect |
|---|---|---|
| 1 | AutoCorrectInsert | Correct a gap or overlap on input (tzdte_from_local). |
| 2 | AutoCorrectRetrieve | Correct a gap or overlap in a calendar result. |
| 4 | RecalculateOffset | Present the instant under IANA rules instead of the stored offset. |
| 8 | UsePreviousIanaGeneration | If the generation is missing, use the highest installed lower one. |
| 16 | UseNextIanaGeneration | If the generation is missing, use the lowest installed higher one. |
Anything else is an error, never ignored: an unknown name or bit, a negative
number, a duplicate, empty or trailing items, spaces, or both generation
directions together. NULL is not a default: Informix returns NULL
for a NULL argument before the function runs, and that is left as it is.
No settings that change results
No configuration parameter, environment variable or session setting alters
what any tzdte function returns. Every alternative behaviour is a
separate, named function or argument written in the SQL. The reasons:
- A function whose result depends on a setting must be marked
VARIANT, which bans it from functional indexes. Marking itNOT VARIANTanyway gives indexes built under one setting and read under another: silent corruption. - Views, stored procedures, triggers and check constraints would behave according to whoever ran them.
- Enterprise Replication peers with different settings would compute different values.
DBDATE,DBCENTURYandGL_DATETIMEare long-standing “works on my box” problems, and PostgreSQL'sTimeZoneand MySQL'ssql_modeshow the same pattern.
The only administrative controls are about where the rules come from and when they are reloaded. They never change what a stored value means. Nothing here changes the caller's isolation level: zone-aware calls read a shared-memory cache and take no table locks.
Where the zone rules come from
Four sources were weighed:
| Source | For | Against | Verdict |
|---|---|---|---|
| The operating system's zone files | Nothing to ship; patched with the OS. | They differ across Linux, AIX and distributions. AIX updates wait for vendor fixes. They change silently under a running server. Distributions compile them differently (slim files, Dublin variants, missing aliases). Secondaries can differ from the primary. | Rejected outright. |
| Files in the DataBlade directory | Same everywhere; the version is controlled. | Every server directory must be updated, including each secondary, and the files are not in backups. | Delivery format only. |
| Compiled into the library | Nothing to lose. | A rule change means a rebuild and a restart. | Only UTC and fixed offsets, which need no data. |
| A table in the database | An HDR secondary gets identical rules automatically (tested on Informix 14.10; RSS and SDS are not tested); archives capture the rules with the data; updates are transactional and auditable; the version is visible on every node. | Needs one load at registration. | Chosen, and the only source. Tables are keyed by IANA generation and insert-only (see below). |
Consequences: UTC, Z, GMT and fixed
offsets work with an empty table. A named zone that isn't loaded raises a clear
error and nothing falls back silently. The bundle files are only a delivery
format: after loading, the server never reads them, and the load procedure only
accepts bundles Oninit builds, with a manifest and checksums, never raw
operating-system files.
AIX and the server's TZ. AIX accepts POSIX
strings (CST6CDT), which hold one rule applied to every year and
so carry no history, and Olson names, resolved through ICU. Its zone data lives
in /usr/share/lib/zoneinfo and is updated by vendor fixes, sometimes
after a change has already taken effect. A TZ change needs a reboot
to take full effect, and oninit keeps the TZ it
started with. POSIX offsets also have the opposite sign (CST6 is
UTC−6). So tzdte never reads the server's TZ:
the same SQL gives the same answer on AIX and Linux.
Storage and identity
| Topic | Decision | Why, and what follows |
|---|---|---|
| Layout | 16 bytes, 8-byte aligned: the UTC instant in microseconds (always a multiple of 10), the zone id, and a packed word holding the offset in effect when the value was written and the IANA generation that produced it. Offset: bits 0–16, in seconds, biased by 65536. Generation: bits 17–30 (0–16383; 0 means none, for UTC and fixed offsets). Bit 31 is reserved and must be 0. | The layout of an opaque type is frozen once data exists and cannot be migrated in place. Validity is checked everywhere a value is used: a corrupt value is an error, never repaired. |
| Offset in seconds | The offset is held in seconds, not minutes. Input offsets are limited to ±18:00:00. | Old local mean time offsets have seconds: Amsterdam before 1937 was +00:19:32. |
| What the stored offset is for | It is the interpretation made when the value was written; the generation says which ruleset produced it. | Default retrieval uses it as it is. tzdte_rules_changed() compares it with the current generation. It plays no part in comparison. |
| Byte order | The stored value uses the host's byte order. | That is fine for HDR and same-platform unload and load. Anything that crosses machines uses the portable form below. |
| Zone ids | Assigned by the bundle. Append-only: new names get the next id; nothing is ever renumbered or deleted. | Stored values reference the id. A serial column, a reload in a different order or a truncate-and-reload would silently point values at the wrong zones. The load procedure refuses a name whose id has changed and an id reused for another name; names are only ever inserted. Every server and database gets the same ids, so replication never has to insert zones and cross-database comparisons agree. |
| Aliases | Each alias has its own row and id. | Output preserves the name the writer used: Europe/Kiev stays Europe/Kiev and US/Central stays US/Central. When IANA renames a zone (Kiev to Kyiv, Godthab to Nuuk), it becomes a link in a later generation but keeps its id; older generations keep the old shape. |
| The tables | tzdte_zones (id and name, global, append-only); tzdte_generations (generation, tzdata version, release date, checksum, counts); tzdte_zone_data keyed by generation and zone id (link target, compiled rules as base64 text, checksum); tzdte_meta (the current generation, set last). | The compiled rules contain NUL bytes and LVARCHAR is a character type subject to code-set conversion, so they are stored as base64: about 5.2 KB for the largest zone, which fits in the row with no smart blobs or blobspaces, and is identical in unload files. The raw bytes are stored unmodified so the checksum matches the manifest. For the 2026a bundle: 447 zones and 151 links, 478 KB of compiled data, the largest zone 3.8 KB and the median 0.7 KB; each further generation adds about the same. SELECT is granted to PUBLIC; writes and the load routines are DBA-only. |
| Bundle encoding | Bundles are built from IANA data in the fat form. | Slim zone files, as written by some current builds of the reference compiler, were found to give wrong results for some zones and dates. The reader still handles the rule string at the end of every file, because every zone relies on it for dates past 2037. |
| Portable binary form | UTC instant (8 bytes, big-endian), the packed offset and generation (4, big-endian), name length (1), then the zone name. Length 0 means a fixed offset. | Used by client/server transfer, binary unload and Enterprise Replication streams. Names, not ids, so the receiver maps the name to its own id and stays safe even if ids differ. Decoding checks the structure only: the generation need not be installed on the receiver, because provenance travels with the value. Big-endian, because AIX on POWER is big-endian and x86 is not, so replication between them would otherwise corrupt values silently. Only same-endian machines have been tested. |
IANA generations
| Topic | Decision | Why, and what follows |
|---|---|---|
| What a generation is | One released IANA ruleset (for example 2026a). Its id is product-controlled (tools/generations.txt, append-only; never reused, not necessarily contiguous) and is the same in every database. | The id is stored in each value, so a value can always be traced to the rules that produced its offset. |
| Immutable and insert-only | tzdte_zone_data is keyed by generation and zone id. A load only inserts; a bundle for a generation or tzdata version that is already installed is refused and nothing changes. Nothing deletes or replaces a generation, and generations are retained for ever. | Nothing scans application tables to see whether a generation is still referenced. Normal upgrades never drop or recreate the tables. |
| The loader | One statement: stage, validate every row and name, insert names, the generation and its rules, verify the checksum, then move the current-generation marker last. | Any failure rolls the whole procedure back and leaves the previous current generation. |
| Marker moves forward only | Installing an older generation (a restore) leaves the current one alone. | Restoring generation N means inserting exactly the data released as N, never regenerating it from a newer release. |
| Generation checksum | SHA-256 over a canonical text of the generation and every zone's name, link and rule checksum, in zone-id order. tzdte_generation_checksum(gen) returns JSON: PASS, FAIL or MISSING. | It detects accidental or unsupported changes to canonical rows. It is not a boundary against a DBA who changes the data and the checksum together. |
| Recalculation | RecalculateOffset (or tzdte_recalc(t)) presents the same instant under the current generation; tzdte_recalc_gen(t, gen) under another. The row is not changed. | A missing generation is an error unless a fallback flag is given; previous means the highest installed lower generation and next the lowest higher one, never the numerically nearest. Default retrieval needs no generation. Which generation RecalculateOffset uses is an interpretation of the specification, to be confirmed with its owner. |
Parallel queries, caching and registration
| Topic | Decision | Why, and what follows |
|---|---|---|
| One table and cache per database | A DataBlade is registered per database, so each database has its own tables, installed generations and cache. | Manifest ids keep cross-database comparisons consistent. tzdte_tzdata_version() and tzdte_current_generation() show each database's current generation. |
| Function groups | Three groups: instant-only (compare, send and receive, streams, exact addition) never touch the cache; zone-aware (input, output, tzdte_local, calendar arithmetic) read the cache from shared memory and never run SQL; administrative (tzdte_reload, tzdte_generation_checksum, tzdte_now) run SQL or keep per-statement state. | The first two groups are registered parallelizable; the administrative group is not, because a routine that runs SQL cannot be. |
| Loading the cache | tzdte_reload() builds it. sysdbopen() calls it at session start, which is cheap when nothing has changed, and the load procedure calls it after a load. | If the cache is empty, zone-aware functions that need rules raise an error rather than loading it themselves; retrieval of UTC and fixed-offset values needs nothing. sysdbopen() must swallow errors so that a tzdte problem can never block a login. |
| One statement, one state | The cache state is held in the function's per-statement state, and states and blocks are never freed until restart. | A concurrent reload cannot change the rules in the middle of a query. Every installed generation is cached, about 0.7 MB each per database; loading old generations lazily is future work. |
| Validate first | Every row is decoded, checksummed and parsed in temporary memory before the shared block is allocated. | A bad row can never leave a half-built cache. A generation that fails validation and is not the current one is left out of the cache (an operation that needs it fails with “required IANA generation is not installed”); a failing current generation makes the reload fail. A changed row is detected at the next cache build and on demand by tzdte_generation_checksum, not on every call. |
| No shared state in the library | Nothing is kept in global or static variables. | Threads can move between virtual processors. |
| Secondaries | An HDR secondary carries the tables automatically: a generation loaded on the primary appears on the read-only secondary with its checksum PASS (Informix 14.10). RSS and SDS are not tested. | Load once on the primary; the next session's sysdbopen() notices the new generation. |
| Enterprise Replication | tzdte values replicate (tested with a two-node ER domain, and with HDR). A generation loaded on one ER node, or on an HDR primary, reaches the other node with its checksum PASS and earlier generations unchanged (Informix 14.10; RSS, SDS and conflicts on a generation load are not tested). | The recommended method is to load the same bundles on every server and compare tzdte_current_generation(), tzdte_tzdata_version() and each generation's checksum across the domain. |
When the rules change
| Topic | Decision | Why, and what follows |
|---|---|---|
| Stored interpretation stays | A new generation never rewrites a row. The value still reads as written, at its stored offset, and the instant is unchanged. | tzdte_rules_changed(t) is true for exactly the rows whose stored offset no longer matches the current generation. tzdte_text(t, 'RecalculateOffset') or tzdte_recalc(t) shows the same instant under the new rules without touching the row. |
| Rebase | tzdte_rebase(t) makes a new conversion of the original wall clock under the current generation: a new instant and a new stored generation. | It is for wall-clock promises such as meetings and schedules, not for records of things that already happened. Schedulers should rebase, or store a local DATETIME and a zone name instead. |
| Zone-dependent expressions in indexes | Stable (stored data only): tzdte_utc, tzdte_local(t), tzdte_offset(t), tzdte_zone, tzdte_stored_generation, tzdte_iso(t), tzdte_text(t) and comparisons. Dependent on the current generation: functions that make a new conversion (tzdte_at, tzdte_from_local, tzdte_add_days, tzdte_add_months, tzdte_trunc_day, tzdte_rebase, tzdte_now) and anything called with RecalculateOffset. | The stable ones give the same answer after any tzdata load, so they are safe in functional indexes, check constraints and fragmentation expressions with no rebuild. Do not index the dependent ones, or rebuild such an index after each load. Until 5 Oct 2026 tzdte_local and tzdte_offset used the current rules and had to be rebuilt. |
| Unload and load | Export prints the local time at the stored offset and adds #<generation> for named zones. | Loading rebuilds the identical value, whatever the rules are now. A value written under a generation that is no longer installed still loads: that generation is only provenance. dbexport writes the same text and the rules tables; dbimport then needs tools/tzdte_dbimport.sh, because dbexport drops the qualifier from the two DATETIME casts (error 201), and the import builds the zone cache itself. |
Clients and distributed queries
| Topic | Detail |
|---|---|
| Clients | dbaccess, ESQL/C and 4GL receive text from the output function. Size client buffers for about 70 characters or more, because zone names such as America/Argentina/ComodRivadavia are long. JDBC getString() works. |
| Cross-database queries | On one server they work when the type is registered identically in both databases. |
| Cross-server queries | db@server:table queries are limited to built-in types on many versions. Cast to text on the remote side. |