Oninit® UDR — tzdte: Examples

Worked queries with their results, from a first table through indexes, daylight-time edge cases, replication and moving an existing DATETIME table across.

The results shown are what the type returns, using the 2026a zone rules (IANA generation 1). Zone names are IANA names and must be written in their exact case; sysmaster:sysdual is used for one-row queries.

The design is deliberately strict: if the input or the situation is in doubt, the operation fails instead of guessing. A local time that does not exist or occurs twice, an offset that disagrees with the zone, an unknown zone name and an unknown flag are all errors. Where a correction is wanted it is asked for with a flag argument, for example 'AutoCorrectInsert'.

Try it yourself

To follow along, use a scratch database where tzdte is installed (see Install), including the zone cache setup in step 3, or run EXECUTE FUNCTION tzdte_reload(); at the start of each session. Then create the sample tables the later examples assume:

dbaccess scratchdb tzdte_demo.sql

The script is on the Demo page, and you can download it directly.

This creates demo_day_events (example 3), events_wall and events_utc (example 8) and events_copy (example 11), dropping and recreating tables of those names, so do not point it at a database you care about. Examples 1 and 14 create their own tables, so work through the page from the top.

1. A table of events

CREATE TABLE events (
    id           SERIAL,
    happened_at  tzdte NOT NULL,
    what         VARCHAR(40)
);
CREATE INDEX events_happened_ix ON events (happened_at);

INSERT INTO events (happened_at, what)
VALUES ('2026-07-01 10:00:00 America/Chicago', 'deploy');
INSERT INTO events (happened_at, what)
VALUES ('2026-07-01 16:00:00 Europe/London', 'alert');
INSERT INTO events (happened_at, what)
VALUES ('2026-07-01 20:30:00 Asia/Kolkata', 'acknowledged');

Each value prints in the zone it was written in, with the offset it was written with (the stored offset; no rules are looked up to print it):

SELECT what, happened_at::LVARCHAR FROM events;

deploy        2026-07-01 10:00:00.00000-05:00 America/Chicago
alert         2026-07-01 16:00:00.00000+01:00 Europe/London
acknowledged  2026-07-01 20:30:00.00000+05:30 Asia/Kolkata

All three are the same moment, 15:00 UTC, so they are equal:

SELECT COUNT(*) FROM events
 WHERE happened_at = '2026-07-01 15:00 UTC'::tzdte;

3

-- each value also records which IANA generation resolved its offset (0 = none, for UTC and fixed offsets)
SELECT what, tzdte_stored_generation(happened_at) AS gen, tzdte_offset(happened_at) AS off
  FROM events;

deploy        1  -18000
alert         1    3600
acknowledged  1   19800

2. Show a value in another zone

SELECT tzdte_at(happened_at, 'Asia/Tokyo')::LVARCHAR
  FROM events WHERE what = 'deploy';
-- 2026-07-02 00:00:00.00000+09:00 Asia/Tokyo

SELECT tzdte_at(happened_at, 'UTC')::LVARCHAR
  FROM events WHERE what = 'deploy';
-- 2026-07-01 15:00:00.00000+00:00 UTC

3. Events on a local day

Ask for a range of instants. The zone rules supply the offset, the range is correct on 23-hour and 25-hour days, and the index on happened_at is used:

SELECT * FROM events
 WHERE happened_at >= '2026-07-02 00:00:00 America/Chicago'::tzdte
   AND happened_at <  '2026-07-03 00:00:00 America/Chicago'::tzdte;

To count events per Chicago day, group by a value derived from the column:

SELECT EXTEND(tzdte_local(tzdte_at(happened_at, 'America/Chicago')),
              YEAR TO DAY) AS chicago_day,
       COUNT(*)
  FROM events
 GROUP BY 1
 ORDER BY 1;

For these four rows, written by different systems:

the result is two events on 2026-07-01 and two on 2026-07-02.

4. Elapsed time across a daylight-time change

Subtracting two tzdte values gives exact elapsed time, not the difference between wall clocks:

SELECT '2026-03-08 04:00 America/Chicago'::tzdte
     - '2026-03-08 01:00 America/Chicago'::tzdte
  FROM sysmaster:sysdual;
-- 0 02:00:00.00000    (the clock skipped 02:00 to 03:00)
-- Both operands are real local times; 02:30 on that day would be refused (example 7).

SELECT '2026-11-01 03:30 America/Chicago'::tzdte
     - '2026-11-01 00:30 America/Chicago'::tzdte
  FROM sysmaster:sysdual;
-- 0 04:00:00.00000    (01:00 happened twice)

5. Adding days: exact or wall-clock

Adding a day-time interval is exact elapsed time. tzdte_add_days keeps the wall-clock time. On 2026-03-08 Chicago skips an hour:

SELECT '2026-03-07 09:00 America/Chicago'::tzdte + INTERVAL(24) HOUR TO HOUR
  FROM sysmaster:sysdual;
-- 2026-03-08 10:00:00.00000-05:00 America/Chicago

SELECT tzdte_add_days('2026-03-07 09:00 America/Chicago'::tzdte, 1)
  FROM sysmaster:sysdual;
-- 2026-03-08 09:00:00.00000-05:00 America/Chicago

SELECT tzdte_add_days('2026-03-08 09:00 America/Chicago'::tzdte, -1)
  FROM sysmaster:sysdual;
-- 2026-03-07 09:00:00.00000-06:00 America/Chicago

-- a wall-clock result that lands in the gap is an error unless you ask for a correction
SELECT tzdte_add_days('2026-03-07 02:30 America/Chicago'::tzdte, 1)
  FROM sysmaster:sysdual;
-- tzdte: local time does not exist in zone (DST gap); use AutoCorrectInsert or give an offset

SELECT tzdte_add_days('2026-03-07 02:30 America/Chicago'::tzdte, 1, 'AutoCorrectRetrieve')
  FROM sysmaster:sysdual;
-- 2026-03-08 03:30:00.00000-05:00 America/Chicago

6. Adding months

Month arithmetic clamps to the last day of the month. YEAR TO MONTH intervals are not accepted by + and -; use tzdte_add_months:

SELECT tzdte_add_months('2026-01-31 12:00 UTC'::tzdte, 1)
  FROM sysmaster:sysdual;
-- 2026-02-28 12:00:00.00000+00:00 UTC

SELECT tzdte_add_months('2026-01-31 12:00 UTC'::tzdte, -13)
  FROM sysmaster:sysdual;
-- 2024-12-31 12:00:00.00000+00:00 UTC

SELECT '2026-01-31 12:00 UTC'::tzdte + INTERVAL(1) MONTH TO MONTH
  FROM sysmaster:sysdual;
-- error 1266: Intervals or datetimes are incompatible for the operation

7. Missing and repeated local times

By default a local time in a gap or an overlap is an error: the database will not guess which instant you meant. Ask for a correction with the AutoCorrectInsert flag, or put the offset in the text:

-- the spring gap: 02:30 does not exist in America/Chicago on 2026-03-08
SELECT tzdte_from_local(DATETIME (2026-03-08 02:30:00) YEAR TO SECOND,
                       'America/Chicago')
  FROM sysmaster:sysdual;
-- tzdte: local time does not exist in zone (DST gap); use AutoCorrectInsert or give an offset

SELECT tzdte_from_local(DATETIME (2026-03-08 02:30:00) YEAR TO SECOND,
                       'America/Chicago', 'AutoCorrectInsert')
  FROM sysmaster:sysdual;
-- 2026-03-08 03:30:00.00000-05:00 America/Chicago

-- the fall overlap: 01:30 happens twice on 2026-11-01
SELECT tzdte_from_local(DATETIME (2026-11-01 01:30:00) YEAR TO SECOND,
                       'America/Chicago')
  FROM sysmaster:sysdual;
-- tzdte: local time is ambiguous in zone (DST overlap); use AutoCorrectInsert or give an offset

SELECT tzdte_from_local(DATETIME (2026-11-01 01:30:00) YEAR TO SECOND,
                       'America/Chicago', 'AutoCorrectInsert')
  FROM sysmaster:sysdual;
-- 2026-11-01 01:30:00.00000-05:00 America/Chicago    (the earliest of the two)

AutoCorrectInsert moves a gap time forward by the real size of the gap (one hour in Chicago, 30 minutes in Lord Howe) and takes the earliest instant in an overlap. A text literal cannot carry a flag, so '2026-03-08 02:30 America/Chicago'::tzdte is an error; use tzdte_from_local with the flag. The flag can be given as the integer 1 as well as by name; names are exact case, separated by commas without spaces, and an unknown or repeated name is an error.

Text that carries an explicit offset needs no flag. The offset decides which one it is:

'2026-11-01 01:30:00-05:00 America/Chicago'
   -> 2026-11-01 01:30:00.00000-05:00 America/Chicago
'2026-11-01 01:30:00-06:00 America/Chicago'
   -> 2026-11-01 01:30:00.00000-06:00 America/Chicago

8. Moving a DATETIME plus zone column across

ALTER TABLE events_wall ADD happened_tz tzdte;

-- wall-clock DATETIME + zone column
UPDATE events_wall SET happened_tz = tzdte_from_local(happened_at, tz);

-- UTC DATETIME + zone column
UPDATE events_utc  SET happened_tz = tzdte_from_utc(happened_at, tz);

Rows that are fine convert silently. A wall-clock row that falls in a gap or an overlap makes the UPDATE stop with the error from example 7, so you find out about every doubtful row instead of having it guessed:

INSERT INTO events_wall (happened_at, tz)
VALUES (DATETIME (2026-03-08 02:30:00) YEAR TO SECOND, 'America/Chicago');

UPDATE events_wall SET happened_tz = tzdte_from_local(happened_at, tz)
 WHERE happened_tz IS NULL;
-- tzdte: local time does not exist in zone (DST gap); use AutoCorrectInsert or give an offset

-- once you have decided that moving such rows forward is acceptable:
UPDATE events_wall SET happened_tz = tzdte_from_local(happened_at, tz, 'AutoCorrectInsert')
 WHERE happened_tz IS NULL;

SELECT happened_tz::LVARCHAR FROM events_wall WHERE id = 4;
-- 2026-03-08 03:30:00.00000-05:00 America/Chicago

Going back, tzdte_utc(t) returns the UTC DATETIME and tzdte_local(t) the wall-clock one. The function reference has the full table of conversions.

9. An offset that disagrees with the zone is refused

When text carries both an offset and a zone, the offset must be the one the zone's rules give for that instant. If it is not, the value is not stored:

INSERT INTO events (happened_at, what)
VALUES ('2026-07-01 10:00:00-06:00 America/Chicago', 'stale');
-- tzdte: offset does not match the zone's rules in the value's IANA generation

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

"The value's generation" is the current one, or the one named by a trailing #<generation> in exported text (example 11). That is how a value written under old rules, with the old offset, loads back unchanged. Example 14 shows the old and the new generation side by side, and how tzdte_rules_changed, tzdte_recalc and tzdte_rebase work on values written under rules that have since changed.

10. Indexes

A B-tree index on a tzdte column orders and matches on the UTC instant, so range and equality predicates use it. (The plan below was captured before the generation change; it does not depend on it.)

SET EXPLAIN ON;

SELECT COUNT(*) FROM events
 WHERE happened_at >= '2021-01-01 00:00 UTC'::tzdte
   AND happened_at <  '2021-02-01 00:00 UTC'::tzdte;

  (1) Index Name: events_happened_ix
      Index Keys: happened_at   (Serial, fragments: ALL)
      Lower Index Filter: informix.greaterthanorequal(...)
      Upper Index Filter: informix.lessthan(...)

A predicate wrapped around the column is not an instant range, so it cannot use that index. Rewrite it as one (example 3), or index the expression. An index on an expression that makes a new zone conversion (example 14) must be rebuilt after each tzdata load; tzdte_local, tzdte_offset and tzdte_utc of a stored value do not change. The overview explains why.

DISTINCT, UNION, joins and ORDER BY work on the column. Because the type cannot be hashed, COUNT(DISTINCT col) fails, and GROUP BY col fails unless an index on the column supplies the order:

SELECT COUNT(DISTINCT happened_at) FROM events;
-- 9643: Type (tzdte) is not hashable.

-- workaround: count over a derived value
SELECT COUNT(DISTINCT tzdte_utc(happened_at)) FROM events;

-- also works: sort-based DISTINCT in a subquery
SELECT COUNT(*) FROM (SELECT DISTINCT happened_at FROM events);

11. Unload and load

UNLOAD writes local time at the stored offset and adds the IANA generation (#1), so LOAD rebuilds the identical instant, zone, stored offset and generation, even after a tzdata update:

UNLOAD TO 'events.unl' SELECT happened_at FROM events;
LOAD FROM 'events.unl' INSERT INTO events_copy (happened_at);

-- events.unl
2026-07-01 10:00:00.00000-05:00 America/Chicago #1|
2026-07-01 16:00:00.00000+01:00 Europe/London #1|
2026-07-01 20:30:00.00000+05:30 Asia/Kolkata #1|

If that generation is no longer installed on the receiving database the value still loads, because the offset is kept as the record of how it was resolved.

12. Replication (Enterprise Replication)

A tzdte column replicates like any other. Both servers need the module, the registration and the same tzdata generations; load the same bundles on each and compare tzdte_tzdata_version() and tzdte_generation_checksum(<gen>). Loading the same bundles on each server is the recommended method, rather than replicating the rules tables themselves. The table needs a primary key:

-- on both servers
CREATE TABLE events_er (
    id           INT PRIMARY KEY,
    happened_at  tzdte,
    what         VARCHAR(40)
);

cdr define replicate -C ignore -S row -A -R events_repl \
    "mydb@g_a:informix.events_er" "select * from events_er" \
    "mydb@g_b:informix.events_er" "select * from events_er"
cdr start replicate events_repl

Rows written on either server arrive on the other with the same instant, zone and stored offset, including a value written in the second hour of a fall-back overlap, a fixed-offset value, the maximum year and NULL:

INSERT INTO events_er VALUES (1, '2026-11-01 01:30-05:00 America/Chicago', 'overlap, first');
INSERT INTO events_er VALUES (2, '2026-11-01 01:30-06:00 America/Chicago', 'overlap, second');
INSERT INTO events_er VALUES (3, '2026-01-15 09:00 +05:30', 'fixed offset');
INSERT INTO events_er VALUES (4, '9999-12-31 23:59:59.99999 UTC', 'maximum');

-- on the other server, a moment later
SELECT id, happened_at::LVARCHAR FROM events_er ORDER BY id;

1  2026-11-01 01:30:00.00000-05:00 America/Chicago
2  2026-11-01 01:30:00.00000-06:00 America/Chicago
3  2026-01-15 09:00:00.00000+05:30
4  9999-12-31 23:59:59.99999+00:00 UTC

Updates (including a change of zone) and deletes replicate too. This was run between two Informix 14.10.FC13W7 servers with one replicate in both directions and conflict rule ignore. Conflicting writes to the same row, a tzdata change on one server only, and ALTER on a replicated tzdte column were not tried. This test was run before the generation was added to the stored value and has not been re-run since.

13. How a clock change looks, hour by hour

A tzdte value is an instant, so UTC never skips or repeats. What changes is the offset the zone applies, and the local clock follows it. Take five consecutive UTC hours around each change in America/Chicago and show each in its own zone:

SELECT tzdte_utc(t) AS utc,
       tzdte_at(t, 'America/Chicago')::LVARCHAR AS chicago,
       tzdte_offset(tzdte_at(t, 'America/Chicago')) AS offset_s
  FROM (SELECT '2026-03-08 05:00 UTC'::tzdte + (tabid - 1) * INTERVAL(1) HOUR TO HOUR
               AS t
          FROM sysmaster:systables WHERE tabid BETWEEN 1 AND 5)
 ORDER BY 1;

Spring forward on 2026-03-08 gives the table below (the zone name America/Chicago that follows each value is left out):

The UTC column steps by one hour every row. The local hour 02:00 never appears: the offset moved from -06:00 to -05:00 between 07:00 and 08:00 UTC. Changing the start to 2026-11-01 05:00 UTC shows falling back:

Here the local hour 01:00 appears twice, an hour apart in UTC, told apart by the offset. Both values are distinct instants and sort in the right order: a column of tzdte keeps the offset it was written with, so a reading taken in the repeated hour is never ambiguous. Compare example 7 for what happens when only a local time is supplied, and example 4 for elapsed time across these changes.

14. When the rules change: what a user sees

This example is hypothetical. Suppose a government decides that from 2027-01-01 America/Chicago stays on daylight time all year. The tzdata release that carries the change is called 2026x here; no such release exists. It is loaded as a second IANA generation; generation 1 (2026a) stays installed. A team keeps its calendar in a tzdte column and books two meetings in advance, both for 09:00 Chicago time:

CREATE TABLE meetings (id INT, what VARCHAR(30), starts tzdte);
INSERT INTO meetings VALUES (1, 'January planning', '2027-01-12 09:00 America/Chicago');
INSERT INTO meetings VALUES (2, 'July review',      '2027-07-14 09:00 America/Chicago');

EXECUTE FUNCTION tzdte_tzdata_version();   -- 2026a

SELECT what, starts::LVARCHAR, tzdte_utc(starts) AS utc, tzdte_stored_generation(starts) AS gen
  FROM meetings ORDER BY id;

January planning  2027-01-12 09:00:00.00000-06:00 America/Chicago  2027-01-12 15:00:00  1
July review       2027-07-14 09:00:00.00000-05:00 America/Chicago  2027-07-14 14:00:00  1

The DBA loads the new tzdata bundle with tzdte_load_tzdata.sql. It is inserted as generation 2; nothing in the table is touched, and nothing already installed is changed. Everyone running the same query still sees what was written:

EXECUTE FUNCTION tzdte_tzdata_version();   -- 2026x
EXECUTE FUNCTION tzdte_current_generation();   -- 2

SELECT what, starts::LVARCHAR, tzdte_utc(starts) AS utc FROM meetings ORDER BY id;

January planning  2027-01-12 09:00:00.00000-06:00 America/Chicago  2027-01-12 15:00:00
July review       2027-07-14 09:00:00.00000-05:00 America/Chicago  2027-07-14 14:00:00

Default retrieval is the stored instant plus the stored offset, so no rule change alters it. A comparison against the instant still matches:

SELECT COUNT(*) FROM meetings WHERE starts = '2027-01-12 15:00 UTC'::tzdte;
-- 1

Because each value records the offset and the generation it was written under, tzdte_rules_changed finds exactly the rows the change hit:

SELECT what, tzdte_rules_changed(starts) AS changed FROM meetings ORDER BY id;

January planning  t
July review       f

To see the same instant under the new rules, ask for it. The row is not changed:

SELECT what, tzdte_text(starts, 'RecalculateOffset') AS recalculated
  FROM meetings ORDER BY id;

January planning  2027-01-12 10:00:00.00000-05:00 America/Chicago
July review       2027-07-14 09:00:00.00000-05:00 America/Chicago

-- the same, returning a tzdte value (stored generation 2) instead of text
SELECT what, tzdte_recalc(starts)::LVARCHAR AS recalculated, tzdte_utc(starts) AS utc
  FROM meetings ORDER BY id;

January planning  2027-01-12 10:00:00.00000-05:00 America/Chicago  2027-01-12 15:00:00
July review       2027-07-14 09:00:00.00000-05:00 America/Chicago  2027-07-14 14:00:00

The July meeting is unchanged, because summer was already daylight time. The January meeting is the same instant, 15:00 UTC, but under the new rules the calendar would say 10:00. Anyone who booked “9 o’clock” would turn up an hour early by their own clock. A new booking is resolved under the current generation and records it:

SELECT '2027-01-12 09:00 America/Chicago'::tzdte::LVARCHAR AS new_booking,
       tzdte_stored_generation('2027-01-12 09:00 America/Chicago'::tzdte) AS gen
  FROM sysmaster:sysdual;

2027-01-12 09:00:00.00000-05:00 America/Chicago  2

-- text that carries the old offset is checked against the current rules, so it is refused ...
SELECT '2027-01-12 09:00-06:00 America/Chicago'::tzdte FROM sysmaster:sysdual;
-- tzdte: offset does not match the zone's rules in the value's IANA generation

-- ... unless it names the generation it was written under
SELECT '2027-01-12 09:00-06:00 America/Chicago #1'::tzdte::LVARCHAR FROM sysmaster:sysdual;
-- 2027-01-12 09:00:00.00000-06:00 America/Chicago

A meeting is a wall-clock promise, so the repair is to keep 09:00 and let the instant move. tzdte_rebase does that: it converts the original wall-clock time again under the current generation, and the row records generation 2:

UPDATE meetings SET starts = tzdte_rebase(starts)
 WHERE tzdte_rules_changed(starts);

SELECT what, starts::LVARCHAR, tzdte_utc(starts) AS utc,
       tzdte_rules_changed(starts) AS changed, tzdte_stored_generation(starts) AS gen
  FROM meetings ORDER BY id;

January planning  2027-01-12 09:00:00.00000-05:00 America/Chicago  2027-01-12 14:00:00  f  2
July review       2027-07-14 09:00:00.00000-05:00 America/Chicago  2027-07-14 14:00:00  f  1

The January meeting now starts an hour earlier in UTC, at 14:00. The July row is untouched and still records generation 1.

Rebasing is the right tool for values that stand for a future wall-clock promise, such as the meetings above. See example 9 for the same check on a single value, and the design decisions for why tzdte keeps the stored interpretation by default.

15. Errors

SELECT '2026-07-01 10:00'::tzdte FROM sysmaster:sysdual;
-- tzdte: tzdte literal needs a UTC offset or zone

SELECT '2026-07-01 10:00:00.123456 UTC'::tzdte FROM sysmaster:sysdual;
-- tzdte: fraction has more than 5 digits; resolution is 10 microseconds
--       (one to five digits are accepted; a sixth digit, even a 0, is an error)

SELECT '2026-07-01 10:00 CST'::tzdte FROM sysmaster:sysdual;
-- tzdte: unknown time zone, or no tzdata loaded (zone names match exactly; ...)
--       (abbreviations are not accepted)

SELECT '2026-07-01 10:00 america/chicago'::tzdte FROM sysmaster:sysdual;
-- tzdte: unknown time zone, or no tzdata loaded (zone names match exactly; ...)
--       (zone names are exact case)

SELECT '2026-07-01 10:00 Mars/Olympus'::tzdte FROM sysmaster:sysdual;
-- tzdte: unknown time zone, or no tzdata loaded (zone names match exactly; ...)

SELECT '2026-07-01 10:00-06:00 America/Chicago'::tzdte FROM sysmaster:sysdual;
-- tzdte: offset does not match the zone's rules in the value's IANA generation

SELECT tzdte_from_local(DATETIME (2026-07-01 10:00:00) YEAR TO SECOND,
                       'America/Chicago', 'Bogus')
  FROM sysmaster:sysdual;
-- tzdte: invalid flags (unknown, duplicate, malformed or conflicting)

SELECT tzdte_add_days('2026-03-07 09:00 America/Chicago'::tzdte,
                     99999999999::INT8)
  FROM sysmaster:sysdual;
-- tzdte: tzdte value out of range (0001-9999)