Oninit® UDR — tzdte: Install

Register the type in a database, load the time zone bundle, and check it works. One database at a time.

Before you start

1. Register the type

dbaccess mydb tzdte_register.sql

This creates the type tzdte, its casts, comparison and arithmetic functions, the zone functions, and the rules tables:

The type is registered CANNOTHASH: equal instants in different zones would otherwise hash differently, so hash joins and hash grouping are not used on tzdte columns.

2. Load the time zone bundle

Download the 2026a bundle (IANA tzdata release 2026a) and keep the files together in one directory:

Check both files against the manifest before loading. The SHA-256 values should be:

7b2f4376f51aee41e758875205b0a2998018925ca9c6806105924b7fa7a7ac43  tzdata_2026a.unl
e56e18f5e5414fa88acae58b42051a0d700d6bbe14cdc3a40caa60689f6c54aa  tzdata_2026a.gen

sha256sum tzdata_2026a.unl tzdata_2026a.gen

Then, from that directory:

cd <directory with the bundle>
ln -sf tzdata_2026a.unl tzdata.unl
ln -sf tzdata_2026a.gen tzdata.gen
dbaccess mydb tzdte_load_tzdata.sql

The script stages the bundle, applies it as generation 1 in one transaction, and reloads the cache. The bundle holds 447 zones and 151 aliases. The load only inserts: it refuses a generation or tzdata version that is already installed, verifies the generation checksum, and moves the current marker last. It never reads the operating system's zone files. A later release loads the same way as a new generation; the old one stays (see Time zone data).

Zone names that need no data work before the load: UTC, Z, GMT and ISO offsets. A named zone that isn't loaded raises a clear error; nothing falls back silently.

3. Pick up the zone cache at session start

Add a call to tzdte_reload() to the database's sysdbopen(). It is cheap when nothing has changed. Errors must be swallowed so that a tzdte problem can never block a login:

CREATE PROCEDURE public.sysdbopen()
  DEFINE v LVARCHAR;
  BEGIN
    ON EXCEPTION END EXCEPTION WITH RESUME;
    LET v = tzdte_reload();
  END;
END PROCEDURE;

If the database already has a sysdbopen(), add the LET line there instead. Until the cache is loaded, zone-aware functions raise an error rather than loading it themselves.

4. Check it

EXECUTE FUNCTION tzdte_tzdata_version();
-- 2026a

EXECUTE FUNCTION tzdte_current_generation();
-- 1

EXECUTE FUNCTION tzdte_generation_checksum(1);
-- {"generation":1,"tzdata_version":"2026a", ... ,"status":"PASS"}

SELECT '2026-07-01 10:00:00 America/Chicago'::tzdte::LVARCHAR
  FROM sysmaster:sysdual;
-- 2026-07-01 10:00:00.00000-05:00 America/Chicago

Moving a database with dbexport and dbimport

dbexport writes your tzdte values with their stored offset and generation, and the rules tables go with them, so the target database needs no separate tzdata load. One thing needs repairing before dbimport can read the export back. dbexport (and dbschema -d) write the two DATETIME casts of tzdte without their YEAR TO FRACTION(5) qualifier, because the system catalog does not record it. A plain dbimport therefore stops at the first of them:

dbimport -c -l -i imp shop2
...
create explicit cast (datetime as tzdte with "informix".tzdte_from_datetime);
*** prepare sqlobj
201 - A syntax error has occurred.

This is in the Informix utilities, so the registration cannot change it. Import with tools/tzdte_dbimport.sh instead. It takes the same arguments as dbimport, rewrites exactly those two statements in <dir>/<database>.exp/<database>.sql, keeps the original as <database>.sql.tzdte-orig, and then runs dbimport. Running it again on a repaired file changes nothing.

dbexport -o exp shop
tools/tzdte_dbimport.sh -c -l -i exp shop
-- tzdte_dbimport: repaired 2 DATETIME cast statement(s) in exp/shop.exp/shop.sql
-- ...
-- dbimport completed

Keep the options you would give dbimport (-c, -l, -d, -i and so on); the database name must be the last argument and is the name to import as: to import under another name on the same server, rename the .exp directory and the .sql file inside it first, as dbimport itself requires. The module must be installed on the target server. The import builds the zone cache itself, so you do not need to run tzdte_reload() first. Then check the result as in step 4:

EXECUTE FUNCTION tzdte_current_generation();
-- 1

EXECUTE FUNCTION tzdte_generation_checksum(1);
-- {"generation":1,"tzdata_version":"2026a", ... ,"status":"PASS"}

SELECT id, what, happened_at::LVARCHAR FROM events ORDER BY id;
-- 1  deploy  2026-07-01 10:00:00.00000-05:00 America/Chicago
-- 2  alert   2026-07-01 16:00:00.00000+01:00 Europe/London
-- 3  ack     2026-07-01 20:30:00.00000+05:30 Asia/Kolkata

Run the checksum check for every generation the database holds. A dbschema -d script needs the same two-line edit by hand.

Notes