Oninit® UDR — tzdte

A time-zone-aware timestamp for Informix® — one indexable value that carries the instant, the zone it was written in, the offset that applied at the time, and which IANA release produced it.

Informix DATETIME has no zone. CURRENT follows the server's TZ, and parsing and display can shift with DBTIME and GL_DATETIME. The usual workaround is a UTC DATETIME plus a zone-name column, which every query then has to remember to combine. tzdte packs both into one 16-byte opaque type that sorts, indexes and compares like a timestamp, and that answers “what time was that in Chicago?” without a second column.

At a glance

Reading and writing values

Input needs a zone, an offset, or both. Text with neither is rejected rather than silently taken as UTC or server time.

2026-07-01 10:00:00 America/Chicago    -- zone: the offset comes from the rules
2026-07-01 15:00:00 UTC                -- built-in UTC
2026-07-01 10:00:00-05:00              -- fixed offset, no zone name
2026-07-01 10:00:00.5 Asia/Kolkata     -- up to 5 fractional digits

Output is always YYYY-MM-DD HH:MM:SS.fffff±HH:MM Zone, from the stored offset. The offset is included so that the repeated hour at the end of daylight time reads back as the same instant:

2026-07-01 10:00:00.00000-05:00 America/Chicago
2026-07-01 15:00:00.00000+00:00 UTC
2026-07-01 10:00:00.00000+05:30 Asia/Kolkata

Zone names are IANA names, matched exactly, including case, plus UTC, Z, GMT and ISO offsets (+05:30); america/chicago is an error. Abbreviations such as CST or IST are rejected because each has several meanings. Alias names keep the spelling the writer used: Europe/Kiev stays Europe/Kiev.

Daylight-time gaps and overlaps

A local time can be missing (spring forward) or occur twice (fall back). By default tzdte does not guess: both are errors. Give an offset to say which occurrence you mean, or ask for correction.

A gap moves forward by the real gap, not always an hour: 02:10 in Lord Howe's 30-minute gap becomes 02:40. A text literal cannot carry flags, so use tzdte_from_local(dt, zone, 'AutoCorrectInsert'). An offset given with a zone must agree with the zone's rules, or it is an error. See the function reference.

Arithmetic

Adding a day-time INTERVAL is exact elapsed time, matching DATETIME and Oracle. Across the spring change, 09:00 CST plus 24 hours is 10:00 CDT. When you mean the same wall-clock time tomorrow, say so with a named function (it errors if that time does not exist, unless you pass AutoCorrectRetrieve):

t                           = 2026-03-07 09:00:00.00000-06:00 America/Chicago
t + INTERVAL (24) HOUR TO HOUR   = 2026-03-08 10:00:00.00000-05:00 America/Chicago
tzdte_add_days(t, 1)              = 2026-03-08 09:00:00.00000-05:00 America/Chicago
tzdte_trunc_day(t)                = 2026-03-07 00:00:00.00000-06:00 America/Chicago

YEAR TO MONTH intervals are not accepted by + and - (error 1266). Use tzdte_add_months, which clamps to the last day of the month: January 31 plus one month is 2026-02-28.

Indexes

A B-tree index on a tzdte column orders and matches on the UTC instant only. The zone and the stored offset are ignored. The type registers compare and the six comparison functions (equal, lessthan, and so on), which is all Informix's default B-tree operator class needs, so a plain CREATE INDEX events_ix ON events (happened_at) works with no custom operator class.

How the index is built. Informix builds the index itself, with its ordinary B-tree code. To the server a tzdte is an opaque 16-byte value, so it cannot order the values on its own: whenever it sorts keys to build the index, or walks the tree to answer a query, it calls the comparison functions registered with the type. compare does the ordering, and equal, lessthan, lessthanorequal, greaterthan and greaterthanorequal answer the predicates. Those functions look only at the UTC instant inside the value. They never read the zone or the stored offset and never touch the zone table, so the index is ordered by instant alone, and a comparison is a plain integer compare.

Each index entry still holds the whole value, zone and offset included; they simply play no part in where the entry goes. Two values for the same instant compare as equal keys, and the index returns whichever one is stored, in the zone it was written in. This is also why the type cannot be hashed: a hash of the raw bytes would put equal instants in different buckets, so the type is registered CANNOTHASH.

See the examples for the queries and the plan an indexed range scan produces.

When the rules change

Governments change daylight-time rules. A new IANA release is installed as a new generation; the old one stays, and nothing is rewritten. A stored value keeps its instant, its offset and the generation that produced it, so it reads exactly as before. tzdte_rules_changed() finds the rows the new rules would move, tzdte_text(t, 'RecalculateOffset') shows one under the new rules without changing it, and tzdte_rebase() recomputes the instant from the original wall-clock time for schedules. tzdte_generation_checksum() verifies an installed generation. See Time zone data.

What's here