Bitemporal Data

Definition

Bitemporal data records two time dimensions for every fact: valid time, when the fact was true in the real world, and transaction time, when the system learned or stored it. Keeping both lets you ask what was true then, and also what you believed then.

In plain terms

Think of two clocks. One tracks the world; the other tracks your knowledge of it. A price may have been valid from January 1, yet only entered in your system on January 5, and corrected a month later. A single date column cannot describe that history; two can.

With both clocks you can answer two different questions:

• What was the price on January 1, according to the best information we have today?
• What did we think the price on January 1 was, on January 20?

The second question is the one historical research needs. It is what Point-in-Time correctness is built on.

Example

The records below are synthetic.

Asked on January 20 about the January 1 price, the answer is 10. Asked on February 10, the answer is 11. Both answers are correct for the moment they were asked. Nothing was overwritten, so both can be reproduced.

Common misunderstandings

• “It is the same as an audit log.” An audit log records changes to the system. Bitemporal data also records when each fact was true, which is a statement about the world.
• “Event time versus processing time in stream processing is the same thing.” It is closely related, but stream systems usually keep only the latest values, not the full history of what they believed.
• “It needs a special database.” Some databases support it natively, but the idea can be implemented with two date columns and a rule that corrections are added, never overwritten.