Types, Constraints & Database Objects

Dates, Times, and Time Zones

Temporal schemas must distinguish instants on the global timeline, civil date-times interpreted under a zone’s rules, dates, and elapsed or calendar intervals—especially across daylight-saving transitions.

An instant identifies one point on the global timeline.
A local date-time is not an instant.
A time-zone ID is a rule set, not merely an offset.
Dates and recurring civil schedules deserve civil types.
Elapsed duration and calendar interval differ.
Vendor type names conceal different semantics.

Temporal value model

DST boundary example for Europe/Belgrade
Civil input	Condition	Required policy
Spring `02:30` on transition day	Gap: clock skips forward	Reject, or explicitly shift
Autumn `02:30` on transition day	Overlap: two candidate instants	Require earlier/later offset
`2026-07-10 09:00 Europe/Belgrade`	One candidate	Resolve to instant; preserve zone if recurrence matters

Define a scheduled fulfillment before using it

CREATE TABLE fulfillment_windows (
  window_id BIGINT PRIMARY KEY,
  order_id BIGINT NOT NULL REFERENCES orders(order_id),
  local_start TIMESTAMP NOT NULL,
  time_zone_id VARCHAR(64) NOT NULL,
  resolved_start TIMESTAMP WITH TIME ZONE NOT NULL,
  CHECK (time_zone_id <> '')
);