Modeling Temporal and Historical Data

CREATE TABLE policy_price (
  id           BIGSERIAL,
  policy_id    BIGINT,
  price        NUMERIC(10,2),
  valid_from   DATE NOT NULL,
  valid_to     DATE NOT NULL,
  recorded_at  TIMESTAMPTZ DEFAULT NOW(),
  superseded_at TIMESTAMPTZ,
  PRIMARY KEY (id)
);
-- Query: what did we know on 2026-01-15 about price for valid 2026-03?
SELECT price FROM policy_price
WHERE policy_id = ? AND '2026-03-01' BETWEEN valid_from AND valid_to
  AND recorded_at <= '2026-01-15'
  AND (superseded_at IS NULL OR superseded_at > '2026-01-15');

SELECT * FROM employees
FOR SYSTEM_TIME AS OF TIMESTAMP '2025-12-31 23:59:59';