Bitemporal data

Bitemporal data assigns two independent time dimensions to a statement. Valid time describes the period during which the statement applied in the represented world. Transaction time describes the period during which it was recorded as part of the database's history. The distinction permits a later correction of an earlier fact without pretending the correction was known earlier. Neither dimension alone answers both historical questions.

In practice, a late notice can change the account of an earlier interval while leaving an intact record of the former account. Queries must state whether they ask what was considered true at a past observation date, what is now believed about that past date, or both. Interval boundaries and time zones need explicit conventions. A timestamp attached to a document is not automatically either valid time or transaction time for every claim it contains.

This matters when an organization must explain a decision made before a correction arrived. A current record might say that a licence expired on Monday, while the decision team learned that fact on Thursday. A bitemporal view can separate the state relevant to Monday from the information available on Tuesday. It complements as-of queries and point-in-time reproducibility; it does not establish that either recorded statement was factually correct.

Several database products offer system-versioned tables, which maintain transaction time automatically; Microsoft's documentation on temporal tables is a common example, and the SQL standard added period definitions and system versioning in 2011. Valid time usually has to be modelled by the application, with explicit start and end columns, because it describes the world and not the database. Lakehouse table formats such as Delta Lake and Apache Iceberg support queries against earlier table versions, which provides transaction time at the level of table snapshots.

Designs have to settle conventions that cause errors if left implicit: whether period ends are inclusive or exclusive, how open-ended periods are represented, which time zone and calendar are used, and what precision is meant. The W3C Time Ontology and RFC 3339 give standard vocabularies and timestamp formats. The article on valid time, transaction time and other clocks explains why observation and publication times often need separate treatment.

Pick a fact that has been corrected and ask the system what it said before the correction. Ask for the same fact as of two valid dates. Confirm that a correction never deletes the earlier row. Confirm the conventions for period ends and time zones in documentation and in a test. Confirm that backdated entries are possible and are visible as such, with the date they were entered.