Bitemporal History

When History Itself Has a History

Most software that tracks property changes — an employee's salary, a customer's address — models a single, linear timeline. But real-world data management often faces a more tangled reality: the history itself gets revised after the fact. A payroll run on February 25 pays Sally $6000, but on March 15 HR informs you that she was actually due $6500 as of February 15. Retroactive changes like this mean the past isn't a fixed sequence of events but something we continually reinterpret.

Bitemporal modeling separates these two temporal perspectives. The actual history (also called valid or effective time) describes the true state of facts over time. The record history (transaction time) captures when we learned those facts. Reality is two-dimensional: what was true, versus what we knew and when.

The Two Time Dimensions

Returns to Sally's case. The actual history shows her salary increased from $6000 to $6500 on February 15. But the record history tells a different story: on February 25, payroll knew only the old $6000 rate; by March 25, the raise was on file. If you query for Sally's salary on February 25 without further qualification, you get an ambiguous answer. Hence the two-parameter API: sally.salaryAt('2021-02-25') becomes sally.salaryAt('2021-02-25', '2021-03-25') — return what we believed on the record date about the actual date.

Visualizing this requires a plane with actual time on one axis and record time on the other. Salary levels occupy regions of that plane. A horizontal slice is the actual history as known at one record moment; a vertical slice shows evolving knowledge about a single actual date.

If HR corrects the raise to $6400 on April 5, the actual history is updated — $6500 never was true. The record history, critically, is not rewritten. We don't change what we thought on March 25; we layer the April revelation on top. This gives an audit trail: you can always reconstruct which values drove past decisions, even those later deemed incorrect.

When Do You Actually Need It?

Bitemporality adds real complexity to any system, and many domains can sidestep it. If changes only take effect when the record is updated (no retroactivity), actual and record history coincide. If you simply record history without ever needing to revise it, ordinary single-dimension time suffices. Even with retroactive changes, you might get away with only actual history if the action that depended on the old value captures its own input data. The payroll system can store the salary at check-issuance time inside the payroll record — no separate record history needed.

You also avoid trouble when the retroactive change arrives before any affected event occurs. But when the change lands after — as it usually does — and old actions based on stale data need reconciliation, bitemporal history earns its keep. The main cost, aside from modeling, is user education. Most people don't expect a historical fact to mutate, let alone to query a history of their own knowledge.

The Append-Only Guarantee

In an ideal world, information is perfect and instant, and history is append-only — new events merely extend the record. The real world lags. Bitemporality restores that elegant property at the record level. The actual history is mutable; you go back and correct it. The record history is forever append-only: your past beliefs are never erased, just superseded by later knowledge layered over the actual timeline. This mechanism reconciles two conflicting needs: correcting the facts and preserving a truthful record of what you once thought.

Beyond the Value Itself

Knowing the correct historical value only solves part of the problem. A retroactive change affects dependent state built on the old numbers. Sally was underpaid by $500; the corrected salary might move her across a tax threshold or pension-eligibility date. Bitemporal history alone cannot compute those downstream effects. One approach is a parallel model that captures the world "as it should have been" had the correct facts been known upfront, and uses that to derive compensating actions. Bitemporal history is a useful building block for such analysis, but it never untangles the whole ball of consequential revisions.

Perspectives Instead of Strict Record Time

Using actual dates as record keys, as in the salary samples above, keeps the example legible. But a production representation uses date ranges for both dimensions: actual_range × record_range.

The record dimension can also accommodate multiple knowledge sources. HR learned about Sally's raise on March 10; payroll learned on March 15. Both are legitimate record times for the same actual fact, depending on whose viewpoint you query. Different departments, and certainly different organizations (like tax authorities), aren't synchronized as they learn facts. An enterprise may designate a single authoritative record keeper for given data, but it cannot control the recording times of external agencies.

This generalizes the concept of a record date into that of a perspective. Rather than tying the second dimension strictly to calendar time, you layer any scenario over the actual history: "according to HR's perspective, the salary was $6400" carries the same structure as a query with an explicit record date. The idea extends cleanly to alternative histories — say, shifting Sally onto a hypothetical 10% across-the-board raise in March — and to forward-looking planning, where successive budgets present different forecasts over the same contemplated timeline.

Layering multiple perspectives over one history is rarely necessary. Where it proves valuable is reasoning about alternatives at once: whether you are auditing what a particular department believed at a given moment, or comparing how competing budget plans treat an identical future. Bitemporal history is the core pattern for dealing with changes over (actual) time compounded by the separate issue of changing knowledge about that time.

Two Ways to Keep Bitemporal History

Once you decide bitemporal history is worth storing, you have to pick a mechanism. There are two broad approaches. The first is to use a bitemporal data structure—encoding the necessary date information directly into the store. That could mean nested date range objects, or a pair of start/end dates in a relational table.

This gives you access to all the bitemporal history, but updating and querying it is awkward. A library that handles bitemporal access can ease that pain.

The alternative is event sourcing. Here the primary store isn't Sally's salary state—it's the stream of changes. Events might look like this:

Note that events supporting bitemporal history must themselves be bitemporal. Each event needs both an actual date (or time) for when it occurred in the world, and a record date (or time) for when it was learned about.

Storing events is conceptually straightforward, but answering a query requires more processing. That cost can be cached by building a snapshot of the application's state. If most consumers only need current actual history, you can build a structure supporting just that, populate it from the events, and keep it current as events arrive. Users who do need bitemporal data can create a more complex structure from the same events—their complexity doesn't burden the simpler model. And those who want to examine actual history as of a different record date can reuse nearly all the code for working with current actual history.

Built-in temporal support in databases exists but remains relatively niche, and teams are rightly cautious about niche technologies for long-lived data. So a homegrown scheme is often the best route.

The usual default simplifies access: treat record time as today. Then processing that only needs current record time can ignore bitemporal complications entirely, and the full two-parameter API—like sally.salaryAt('2021-02-25', '2021-03-25')—is reserved for clients that genuinely need it.

Terminology and Sources

The terms "valid time" and "transaction time" come from Richard Snodgrass, whose Developing Time-Oriented Database Applications in SQL goes deep into this problem and influenced the SQL:2011 standard. But early workshops found those terms confusing, so "actual" and "record" stuck instead. Since valid/transaction hasn't become widespread usage, this article follows that lesson. The notion of perspective here comes from Time Travel: A Pattern Language for Values That Change.

Bitemporality can apply to the future too. If Sally is told on May 5 that she'll get an increase on May 12, that increase can be recorded with a record time of May 5 and an actual time of May 12—though in history, actual time is always at or before record time.

The author's earlier writing on parallel models in the mid-2000s touched this topic without continuing, and he's unsure when or if he'll revisit it.