How would you model a value that changes over time — say a product's price or an employee's salary — so the system can answer "what was it on 1 March 2024?", and what are the pitfalls in choosing the interval columns?

## The model An effective-dated (or *valid-time*) table stores one row per period during which a fact held: ``` CREATE TABLE product_price ( product_id BIGINT NOT NULL, price NUMERIC(12,2) NOT NULL, valid_from TIMESTAMPTZ NOT NULL, valid_to TIMESTAMPTZ NOT NULL DEFAULT TIMESTAMPTZ '9999-12-31 00:00:00Z', PRIMARY KEY (product_id, valid_from) ); ``` The entity is identified by `product_id`; the primary key includes the period start because a product legitimately has many rows. "What was the price at time `t`?" becomes a single predicate: ``` SELECT price FROM product_price WHERE product_id = 42 AND valid_from <= :t AND valid_to > :t; ``` ## Half-open intervals, and why they matter Use `[valid_from, valid_to)` — start inclusive, end exclusive. Consecutive periods then share a boundary instant: a price effective from 1 March has `valid_to = 2024-03-01` on the previous row and `valid_from = 2024-03-01` on the new one. Exactly one row matches any instant, with no gap and no overlap. The alternative, closed intervals (`valid_to` is the last valid instant), forces the previous row to end at `2024-02-29 23:59:59.999999`, which depends on your timestamp precision and breaks the moment someone changes the column type or the granularity. It also makes "is this period adjacent to that one?" arithmetic instead of equality. Half-open is the convention in the SQL standard's period support and in every serious temporal design; say so explicitly. ## "Current" — NULL or sentinel `valid_to IS NULL` reads as "no end yet", which is semantically honest, and it lets a partial unique index enforce "at most one open row per product". The cost is that every point-in-time predicate becomes `(valid_to IS NULL OR valid_to > :t)`, and NULLs in comparisons are a reliable source of bugs. A far-future sentinel (`9999-12-31`) gives one uniform predicate, works with `BETWEEN`-style reasoning, makes range types and exclusion constraints straightforward, and indexes cleanly. Its downsides are the magic value itself and the fact that some drivers or engines have limits near the maximum timestamp — pick a safe value and define it once. Either is fine; inconsistency is not. Half a schema on each convention guarantees wrong results at the boundaries. ## Preventing overlaps and gaps This is where hand-rolled designs usually fail. Two rows covering the same instant make the "what was it at t" query return two prices, and application code that takes the first row silently produces different answers depending on plan order. Defences, strongest first: 1. **Exclusion constraint over a range type** — the database rejects any insert whose period overlaps an existing period for the same entity. Where available this is the correct answer; it is declarative and race-proof. 2. **A trigger** that checks for overlap on insert/update. Works everywhere, but must lock the entity's rows to be safe under concurrency. 3. **Application procedure plus a unique key** on `(entity_id, valid_from)` and a partial unique index on the open row. This stops the two most common concrete mistakes (duplicate start instants, two open rows) even without full overlap protection. Also decide whether **gaps** are legal. For a price they usually are not — the product always has a price — so a gap is a bug. For an assignment ("who managed this team") gaps are meaningful. Say which you intend. ## Writing a change A change is never an `UPDATE` of the value. It is, in one transaction: close the currently-open row by setting `valid_to = :effective_at`, then insert a new row with `valid_from = :effective_at`. Doing this in two transactions leaves either an instant with no row or an instant with two. Because the effective instant is an input rather than "now", the same mechanism naturally handles **future-dated** changes (a price rise scheduled for next month) — the future row simply exists ahead of time. **Backdated** changes are harder: inserting a row effective from last week means splitting or closing rows that already exist, and it silently rewrites what a report run yesterday would have said. If you need to preserve what the system believed at the time, valid time alone is insufficient and you need a second time axis. ## Query shapes and indexing - **Current row**: `WHERE valid_to = '9999-12-31'` (or `IS NULL`) — supported by a partial index, and by far the most frequent read. Many teams additionally denormalize the current value onto the parent row for hot paths, accepting the duplication. - **As-of join**: joining orders to the price that applied at each order's timestamp — `ON p.product_id = o.product_id AND p.valid_from <= o.placed_at AND p.valid_to > o.placed_at`. Support it with an index on `(product_id, valid_from DESC)`; range-typed columns can use a GiST-style index where available. - **Timeline**: all rows for an entity ordered by `valid_from`. ## Practical details that bite - **Granularity.** Dates or timestamps? A salary effective "from 1 April" is a date fact; a price change at 14:03 is an instant. Mixing them creates boundary bugs. Choose per table. - **Time zones.** Store instants in UTC (`TIMESTAMPTZ`), and be explicit that "from 1 April" means a specific zone's midnight — payroll and billing get this wrong constantly. - **Foreign keys.** A child row referencing a temporal parent generally references the entity (`product_id`), not a specific version. If it must pin a version, it needs the period too, which is what the standard's temporal foreign keys address. - **Deleting.** "Remove the price" usually means closing the open period, not deleting rows; deleting destroys the history you built the table for.