Oninit® UDR — tzdte: Function reference

Every function, cast and operator that ships with tzdte.

Defaults are strict: if in doubt, fail. A local time that does not exist or occurs twice, an unknown or wrongly spelled zone, an offset that disagrees with the zone, and a bad flag are all errors, never guesses. Correction is opt-in, through the flags below.

Flags

Functions marked [flags] take one optional last argument, either an INTEGER bitmap or an LVARCHAR of names. Omitted, 0 and '' all mean the defaults.

• Text form: names separated by commas, exact case, no spaces: 'AutoCorrectInsert,RecalculateOffset'.
• Anything else is an error: an unknown name or bit, a negative number, a duplicate, an empty or trailing item, spaces, or both generation directions.
• A flag never corrects a zone name, guesses a zone, picks a generation for you or hides a corrupt value.
• A text literal cannot carry flags, so use tzdte_from_local where you need AutoCorrectInsert.

Constructing values

Taking values apart

Casts

There is no implicit cast between DATETIME and tzdte: it would silently treat CURRENT, which is server-local time, as UTC.

Converting to DATETIME

Which DATETIME you get depends on which time you ask for:

• The cast gives the UTC time, not the local time. Use tzdte_local() for local time.
• The result is DATETIME YEAR TO FRACTION(5), which has the same 10-microsecond resolution as a tzdte, so the cast loses nothing.
• The zone and the stored offset are not kept. Keep the zone (tzdte_zone(t)) alongside if you need to rebuild the value later with tzdte_from_utc(dt, zone) or tzdte_from_local(dt, zone).

Comparison

=, <>, <, <=, >, >= and compare() use the UTC instant only. The zone is metadata. They never touch zone data, so they are cheap in sort-merge joins and index scans.

• DISTINCT keeps an arbitrary zone among equal instants. GROUP BY on the column does too when an index on the column supplies the order. Otherwise it fails with error 9643, as does COUNT(DISTINCT col): the type cannot be hashed. Group or count over a derived value instead, such as GROUP BY tzdte_utc(col).
• A unique index rejects 10:00 America/Chicago if 15:00 UTC is already present, in July.

Arithmetic

Rule changes and generations

A value keeps the offset and the IANA generation it was written with, and is displayed from those. A rule change never rewrites a row. These functions find and act on the difference:

Administration

Errors

Examples

-- 09:00 Chicago on the day before the spring change
SELECT tzdte_from_local(DATETIME (2026-03-07 09:00:00) YEAR TO SECOND,
                       'America/Chicago')::LVARCHAR
  FROM sysmaster:sysdual;
-- 2026-03-07 09:00:00.00000-06:00 America/Chicago

-- the same instant in Tokyo
SELECT tzdte_at('2026-07-01 10:00:00 America/Chicago', 'Asia/Tokyo')::LVARCHAR
  FROM sysmaster:sysdual;
-- 2026-07-02 00:00:00.00000+09:00 Asia/Tokyo

-- a local time inside the spring gap is an error ...
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

-- ... unless you ask for it to be corrected
SELECT tzdte_from_local(DATETIME (2026-03-08 02:30:00) YEAR TO SECOND,
                       'America/Chicago', 'AutoCorrectInsert')::LVARCHAR
  FROM sysmaster:sysdual;
-- 2026-03-08 03:30:00.00000-05:00 America/Chicago

-- the same instant under the current rules, on request
SELECT tzdte_text(starts_at, 'RecalculateOffset') FROM meetings;