bitemporal

Bitemporal refers to a data management approach that records both the validity of a fact in the real world and the time period during which the fact is stored in the database. In a bitemporal model, two time dimensions are tracked: valid time, which marks when a fact is true in the modeled reality, and transaction time, which marks when the database stored or became aware of the fact. An item can be valid during one interval while being known to the system at a different interval.

In practice, records carry two pairs of endpoints: a valid time interval (valid_from, valid_to) and a transaction time interval (tx_from, tx_to). This structure allows queries that ask what was true at a given real date and what the system knew at a given moment. Bitemporal data supports complex histories, such as backdated edits, delayed discoveries, or retroactive corrections, by maintaining separate histories for truth in the world and truth in the database.

Implementation often combines dedicated database features with application design. Some databases support system-versioned or temporal tables to capture transaction time, while valid-time is typically implemented with additional columns or specialized data types. Integrity constraints may enforce non-overlapping intervals for the same entity and consistent alignment of the two timelines. Bi-temporal models can increase storage and processing requirements but enable precise auditing, compliance reporting, and retrospective analyses.

Common use cases include regulatory auditing, financial history tracking, data lineage and governance, and any scenario requiring reconstruction of past states and knowledge. While powerful, bitemporal systems add complexity, requiring careful design of schemas, queries, and performance considerations.