The Bitemporal Problem: When "When" Has Two Different Answers
Most databases that track history track one kind of time: when something was true in the real world. A customer's address was this until that date, then it became something else. A price held until a certain day, then changed. This is already more sophisticated than a database that only stores the present, and it answers a lot of useful questions. But it quietly assumes something that isn't always true: that the database knew about each change at the moment it happened.
In reality, the database usually finds out later. The address changed on the first of the month, but nobody updated the system until the fifteenth. That two-week gap, between when something became true and when the database learned it, is the heart of the bitemporal problem, and handling it correctly is one of the more subtle challenges in data modeling.
The word bitemporal means two timelines, and naming them is the key to understanding the whole idea. The first timeline is what's usually called valid time: when a fact was actually true in the real world. The customer lived at the old address until the first, and at the new one after. The second is what's called transaction time, or system time: when the database recorded that fact, when the system actually knew it. The customer's move happened on the first, but the database didn't know until the fifteenth.
Most of the time these two timelines are close enough that nobody distinguishes them. But they are genuinely different things, and the gap between them, however small, is where a surprising number of important problems live. The bitemporal approach is to track both timelines explicitly, so the database can answer questions not just about what was true, but about what it believed and when.
To see why that second timeline matters, consider a question that sounds strange at first but turns out to be essential: what did we think was true last week? Not what was actually true, but what the database believed at that moment. A system that only tracks valid time can't answer this. It knows the customer's address history, but it has overwritten any record of when it learned each piece. A bitemporal system can answer it, because it kept track of its own knowledge over time as well as the facts themselves.
This distinction stops being academic the moment money or accountability is involved. Imagine a financial report generated on a Monday, based on the data the system held that day. On Wednesday, someone discovers that one of the underlying figures was wrong, a transaction had been recorded incorrectly, and they fix it. Now the data is correct. But the report from Monday still exists, and it was based on the old, wrong figure. Was the report wrong? In a sense, no: it accurately reflected what the organization knew on Monday. The figure was corrected later. To explain the discrepancy, and to defend the Monday report to an auditor, you need to be able to reconstruct exactly what the data looked like on Monday, before the correction. That requires transaction time. Valid time alone can't do it.
This is why bitemporal modeling shows up so often in regulated and financial contexts. When an organization has to be able to prove not just what was true but what it knew and when it knew it, both timelines become mandatory. An auditor asking "what did this account balance appear to be on the date you filed?" is asking a transaction-time question, and the only honest answer comes from a system that recorded its own evolving knowledge. Corrections, restatements, and the ability to defend a past decision all depend on being able to separate when something was true from when you found out.
The reason this is hard is that tracking two timelines at once multiplies the complexity considerably. A single fact, like a customer's address, now has to be situated in two dimensions of time simultaneously: the period it was valid in the real world, and the period the database believed it. A correction doesn't erase the old belief; it records that the old belief was held until a certain moment and then superseded. Querying such data means specifying both which point in real-world time you care about and which point in the database's knowledge you're asking from. The mental model is genuinely two-dimensional, and it takes some effort to hold both axes in mind at once.
A useful way to picture it is a grid. One axis is valid time, when things were true. The other is transaction time, when the database knew. Any question about the data is really a point on that grid: what was true at this real-world moment, according to what the database believed at this other moment. Most questions people ask casually collapse the grid down to a single point, "what's the address now, as far as we currently know", but the full grid is always there, and bitemporal modeling is what keeps all of it accessible.
For most everyday applications, this is more machinery than the situation needs, and tracking a single timeline is perfectly adequate. The bitemporal approach earns its complexity in specific situations: where corrections to past data are common and consequential, where you must be able to reconstruct exactly what was known at any past moment, and where being able to distinguish "we were wrong" from "we changed our mind" carries real weight. In those settings, the seemingly philosophical distinction between when something was true and when you found out becomes the difference between a system that can account for itself and one that can't. The two answers to "when" are both real, and some problems can only be solved by keeping track of both.