Oninit® UDR — tzdte: Demo

A small script that creates the sample tables the Examples page assumes, so you can run the examples as you read them.

What it creates

The Examples page creates events and meetings itself (examples 1 and 14), so the script does not. The script does not touch sysdbopen() either.

Run it

1. Use a scratch database. The script drops and recreates tables with the names above, so do not run it in a database you care about.
2. tzdte must be installed in that database with the time zone data loaded (see Install). Zone-aware calls also need the zone cache: set up sysdbopen() as in Install step 3, or run EXECUTE FUNCTION tzdte_reload(); at the start of each session.
3. Download tzdte_demo.sql and run it:

dbaccess scratchdb tzdte_demo.sql

It is safe to run again; it recreates only its own four tables. The sample rows avoid local times that fall in a clock change, because tzdte refuses those unless you ask for a correction (example 7). Then work through the Examples page from example 1.

The script

-- tzdte_demo.sql - sample tables for trying the examples at
-- https://www.oninit.com/udr/tstz-examples/
-- Run in a scratch database that already has tzdte registered and the tzdata
-- loaded (see the Install page): dbaccess scratchdb tzdte_demo.sql
-- Safe to run again: it drops and recreates only the tables below.
--
-- The examples page creates events and meetings itself (examples 1 and 14),
-- so they are NOT created here. Work through the page from example 1.
--
--   demo_day_events   example 3: four events written by different systems
--   events_wall       example 8: wall-clock DATETIME + zone name
--   events_utc        example 8: UTC DATETIME + zone name
--   events_copy       example 11: target for LOAD
--
-- It does not touch sysdbopen(). Zone-aware calls need the zone cache: install
-- the guarded sysdbopen() from the Install page, or run
--   EXECUTE FUNCTION tzdte_reload();
-- at the start of each session. Use a scratch database.
--
-- None of the local times below falls in a daylight-saving gap or overlap: a text
-- literal or a DATETIME + zone that does is an error by default (README D2), which
-- the examples page shows separately. The tz columns hold the zone NAME as text and
-- are combined with the DATETIME by tzdte_from_local()/tzdte_from_utc() in example 8.

-- demo_day_events (example 3): the same two days written by different systems, some in
-- Chicago time and some in UTC. Around midnight the UTC date and the Chicago date
-- differ, which is what the grouping examples show. Each literal carries its zone, so
-- each value stores the instant, the zone, the offset and the generation.
DROP TABLE IF EXISTS demo_day_events;
CREATE TABLE demo_day_events (
    id           SERIAL,
    happened_at  tzdte NOT NULL,
    what         VARCHAR(40)
);
INSERT INTO demo_day_events (happened_at, what) VALUES ('2026-07-01 23:30 America/Chicago', 'late shift');
INSERT INTO demo_day_events (happened_at, what) VALUES ('2026-07-02 04:45 UTC',             'batch job');
INSERT INTO demo_day_events (happened_at, what) VALUES ('2026-07-02 00:15 America/Chicago', 'backup');
INSERT INTO demo_day_events (happened_at, what) VALUES ('2026-07-02 07:00 UTC',             'report');

-- events_wall (example 8): the legacy pattern, a plain DATETIME holding LOCAL wall-clock
-- time plus the zone name in a separate column. Without the zone name the row would not
-- identify an instant. The third row is in winter on purpose (15 December, standard time).
DROP TABLE IF EXISTS events_wall;
CREATE TABLE events_wall (
    id           SERIAL,
    happened_at  DATETIME YEAR TO SECOND,
    tz           VARCHAR(40)
);
INSERT INTO events_wall (happened_at, tz) VALUES (DATETIME (2026-07-01 10:00:00) YEAR TO SECOND, 'America/Chicago');
INSERT INTO events_wall (happened_at, tz) VALUES (DATETIME (2026-07-01 16:00:00) YEAR TO SECOND, 'Europe/London');
INSERT INTO events_wall (happened_at, tz) VALUES (DATETIME (2026-12-15 08:30:00) YEAR TO SECOND, 'America/Chicago');

-- events_utc (example 8): the other legacy pattern, a DATETIME holding UTC plus a zone
-- name used only for display. All three rows are the same instant, 15:00 UTC.
DROP TABLE IF EXISTS events_utc;
CREATE TABLE events_utc (
    id           SERIAL,
    happened_at  DATETIME YEAR TO SECOND,
    tz           VARCHAR(40)
);
INSERT INTO events_utc (happened_at, tz) VALUES (DATETIME (2026-07-01 15:00:00) YEAR TO SECOND, 'America/Chicago');
INSERT INTO events_utc (happened_at, tz) VALUES (DATETIME (2026-07-01 15:00:00) YEAR TO SECOND, 'Europe/London');
INSERT INTO events_utc (happened_at, tz) VALUES (DATETIME (2026-07-01 15:00:00) YEAR TO SECOND, 'Asia/Kolkata');

-- events_copy (example 11): an empty table with one tzdte column, the target for LOAD
-- when the example reloads rows that UNLOAD wrote.
DROP TABLE IF EXISTS events_copy;
CREATE TABLE events_copy (happened_at tzdte);