Advanced SQL

Temporal and Versioned Queries

Temporal querying models facts over explicit validity intervals; half-open boundaries make adjacency unambiguous, while as-of, overlap, and history-integrity queries preserve the difference between valid time and recorded system time.

Use half-open intervals [valid_from, valid_to).
An as-of predicate contains one instant.
Two half-open intervals overlap when each starts before the other ends.
Valid time and system time answer different questions.
Open-ended history needs a consistent representation.
Version integrity is a write-time concern.

Half-open price history

As-of and overlap checks with null as infinity

-- Version active at the instant
SELECT product_id, price
FROM product_price_history
WHERE product_id = :product_id
  AND valid_from <= :as_of
  AND (valid_to IS NULL OR :as_of < valid_to);

-- Conflicting pairs; adjacency is allowed
SELECT a.product_id, a.valid_from, a.valid_to, b.valid_from, b.valid_to
FROM product_price_history AS a
JOIN product_price_history AS b
  ON b.product_id = a.product_id
 AND (b.valid_from, b.version_id) > (a.valid_from, a.version_id)
 AND (b.valid_to IS NULL OR a.valid_from < b.valid_to)
 AND (a.valid_to IS NULL OR b.valid_from < a.valid_to);

-- PostgreSQL interprets a NULL upper bound as unbounded here.
SELECT * FROM product_price_history
WHERE product_id = :product_id
  AND tstzrange(valid_from, valid_to, '[)') @> :as_of;

Boundary truth table
A	B	Overlap?
`[Jan 1, Mar 1)`	`[Mar 1, Jun 1)`	No; exactly adjacent
`[Jan 1, Apr 1)`	`[Mar 1, Jun 1)`	Yes; Mar 1–Apr 1
`[Jan 1, NULL)`	`[Jun 1, NULL)`	Yes; both are open-ended