Oninit® UDR — tzdte: Comparison

How tzdte relates to the time-zone-aware types other engines ship, and what to change when migrating SQL.

At a glance

Three short tables, one per question: what is stored, how it compares and where the rules come from, and what it depends on.

Engine	What is stored	Keeps the zone name	Displays in
tzdte	UTC instant, zone id, resolved offset, IANA generation id	Yes	The stored offset (the interpretation when written); IANA recalculation on request
PostgreSQL `timestamptz`	UTC instant only	No	Session `TimeZone`
Oracle `TIMESTAMP WITH TIME ZONE`	UTC and zone region (or offset)	Yes	The value's own zone
SQL Server `datetimeoffset`	Local time and offset	No (offset only)	The stored offset
MySQL `TIMESTAMP`	UTC instant only	No	Session `time_zone`
Informix `DATETIME`	Wall-clock digits, no zone	No	As stored

Engine	Equality	Daylight-time aware	Rules come from
tzdte	Same instant	Yes	Tables in the database, one immutable generation per IANA release
PostgreSQL	Same instant	Yes	Bundled IANA copy
Oracle	Same instant	Yes	Oracle time zone file
SQL Server	Same instant	Only via `AT TIME ZONE`	Windows
MySQL	Same instant	Yes, via session zone	Tables loaded from OS files
Informix `DATETIME`	Same digits	No	None

Engine	Result depends on a session setting	Range
tzdte	Never	0001–9999
PostgreSQL	Yes	4713 BC–294276 AD
Oracle	Some	−4712–9999
SQL Server	No	0001–9999
MySQL	Yes	1970–2038
Informix `DATETIME`	Yes (`TZ`, `DBTIME`, `GL_DATETIME`)	0001–9999

Where tzdte behaves the same

Equality and ordering use the instant, as in every engine with a zone-aware type.
A missing local time in a spring-forward gap is an error by default, as in Oracle (ORA-01878). When told to correct it (AutoCorrectInsert), 02:30 becomes 03:30, as in PostgreSQL and SQL Server AT TIME ZONE.
Adding a day-time interval is exact elapsed time, as in Oracle.
The zone name is kept, as in Oracle, so output shows America/Chicago rather than -05:00.

Where it differs

Topic	Others	tzdte
Zone of display	PostgreSQL and MySQL show every value in the session's zone.	Each value shows in its own zone, at its stored offset, so output never depends on who runs the query.
Stored offset and generation	No other engine keeps the offset in effect at write time, and the ruleset that produced it, alongside a zone.	Kept, so default retrieval needs no rules, and values affected by a tzdata update can be found, recalculated or rebased on request.
After a rule change	Oracle relies on a DBA-run upgrade process. PostgreSQL and MySQL shift local times silently.	Instant and displayed local time stay as written; affected rows are findable with `tzdte_rules_changed()` and recalculable with `tzdte_recalc()` or repairable with `tzdte_rebase()`.
Where rules come from	Files, the registry, or tables loaded from the OS.	Only tables in the database, one immutable generation per IANA release, kept for ever, so a value can always be traced to the rules that produced it.
Missing local time	Oracle raises ORA-01878 on a literal in the gap; PostgreSQL moves forward.	An error by default; forward by the real gap with `AutoCorrectInsert`.
Repeated local time	SQL Server uses the earlier instant. Oracle uses standard time or errors. PostgreSQL takes the later instant.	An error by default; the earliest instant with `AutoCorrectInsert`; an explicit offset picks one.
Offset and zone that disagree	Oracle and others use the region or raise an error.	An error: the offset must match the zone's rules in the value's generation. An export carries its generation, so it still reloads.
Zone-name case	PostgreSQL and Oracle ignore case.	Exact match.
`+ 1 day`	PostgreSQL keeps wall-clock time.	Exactly 24 hours. Use `tzdte_add_days` for wall-clock time.
Month arithmetic	PostgreSQL clamps at a month end. Oracle raises ORA-01839.	A `YEAR TO MONTH` interval is not accepted by `+` and `-`. `tzdte_add_months` clamps.
Zone abbreviations	PostgreSQL accepts many; Oracle accepts `TZD`.	Rejected. Only IANA names, `UTC`/`Z`/`GMT` and ISO offsets.
Hash joins	Supported.	Not used. Sort-merge or indexed nested loops instead.
Session settings	PostgreSQL `TimeZone`, MySQL `time_zone`, Oracle `ERROR_ON_OVERLAP_TIME`.	None. Views, triggers, functional indexes and replicated servers all see one answer.
`DATETIME` conversion	Implicit in most engines, using the session zone.	Explicit only, and the `DATETIME` is taken as UTC.
Resolution	Oracle nanoseconds, SQL Server 100 ns.	10 microseconds.

Migrating SQL

From	What to change
PostgreSQL	Replace `+ INTERVAL '1 day'` with `tzdte_add_days(t, 1)` where wall-clock time matters. Don't rely on a session `TimeZone` for display; use `tzdte_at(t, 'zone')`.
Oracle	ORA-01878 behaviour is the default here (an error in a gap). Replace `DBMS_DST` procedures with `tzdte_rules_changed()` and `tzdte_rebase()`.
SQL Server	A repeated local time is an error unless you give an offset or `AutoCorrectInsert`, which takes the earliest instant (`AT TIME ZONE` also uses the earlier one). Map Windows zone names (`Central Standard Time`) to IANA names (`America/Chicago`).
MySQL	Remove any dependence on `SET time_zone`; values carry their own zone.
Informix `DATETIME` plus a zone column	Combine both with `tzdte_from_local(dt, zone)` or `tzdte_from_utc(dt, zone)`. `tzdte_utc(t)` and `tzdte_local(t)` return the two `DATETIME` forms.