← Daily

Concept · No. 032 · November 17, 2026

Bitemporal tables answer what was true and what you knew then

Most tables overwrite the past, so they cannot explain a report that was correct when it ran. Tracking valid time and recorded time separately fixes that, at a cost worth understanding before you pay it.

The idea

A typical table stores current state. An UPDATE replaces the old value, and the old value is gone.

A plain history table helps, but it only tracks one kind of time. There are two:

  • Valid time: when the fact was true in the world, such as a contract price effective from a given date.
  • Recorded time: when the database learned the fact and when it stopped believing it.

These differ whenever information arrives late or gets corrected. A bitemporal table keeps both axes on every row. You can then ask the question auditors, accountants and support engineers actually ask: what did we believe on this date about what was true on that date. Without it, you cannot reproduce a report, explain a past invoice, or tell a correction apart from a change.

An example

A customer's contract sets a new unit price for a part, effective 1 February. Someone enters it on 10 February. An invoice went out on 5 February at the old price.

The price table has valid_from, valid_to, recorded_from and recorded_to.

  • Before 10 February there is one row: old price, valid from the contract start onward, recorded from its entry onward.
  • On 10 February nothing is updated in place. That row's recorded_to is closed at 10 February.
  • Two rows are inserted, both recorded from 10 February: the old price valid until 1 February, and the new price valid from 1 February.

Now two queries give two honest answers. Asking for the 5 February price as known on 5 February returns the old price, which explains why the invoice looks the way it does. Asking with current knowledge returns the new price, which tells you the invoice needs a correction note. Both answers are true, and the table holds both.

Where it breaks

Every query needs predicates on both axes. Forget one in a join and you get duplicate rows that look plausible. This is the most common bug, and it is silent.

Ordinary constraints stop working. A primary key on the business ID no longer holds, because one ID has many rows. You need constraints that forbid overlapping ranges, and foreign keys between temporal tables are not something most engines enforce natively.

Recorded time taken from application clocks drifts and reorders. History that must never change also collides with erasure requests, so personal data may need to live elsewhere.

It is the wrong tool for data that is never retroactively corrected, for caches and derived tables, and where an append-only event log already gives you replay.

In practice

I would apply it narrowly and enforce it in the database rather than in convention.

  • Limit it to facts that get backdated or corrected: prices, rates, contracts, assignments, tax rules.
  • Use half-open ranges, start inclusive and end exclusive, so adjacent periods never overlap or leave gaps.
  • In Postgres, use range types with an exclusion constraint via btree_gist to reject overlapping valid ranges among current rows for the same key.
  • Set recorded time from the database transaction timestamp, never from the client.
  • Revoke UPDATE and DELETE on the table except for closing recorded_to, ideally through one stored procedure.
  • Expose a current_ view and an as-of function taking both dates, so most code never writes the predicates by hand.
  • Store who made each correction and why.
  • Test with a correction that lands inside an earlier correction. That case finds the bugs.
When a fact can be corrected after the fact, store when it was true and when you knew it as separate columns, and never update either in place.