Bitemporal Data

Two axes, set by different parties

• Valid time (application time) is supplied by the user from evidence: effective dates, completion dates, as-of dates. It can be in the past, present or future and can be corrected.
• Transaction time (system time) is set by the database. In SQL:2011 system-versioned tables, users cannot assign or change it; an insert sets the start to the transaction time stamp, and an update or delete closes the current row and keeps it as a historical row. Kulkarni and Michels note that this guarantees the recorded history of changes cannot be tampered with, which matters for audit and compliance.

Transaction time is not the date a source was captured. A document captured on Monday and loaded on Wednesday has a Monday capture date (an evidence date) and a Wednesday transaction time.

How SQL:2011 implements it

• A period is declared on a pair of date or timestamp columns (PERIOD FOR ...). Periods are closed-open.
• A table with a user-named period is an application-time period table; one with the SYSTEM_TIME period and WITH SYSTEM VERSIONING is system-versioned; a table can be both.
• Queries can ask for any past system state: FOR SYSTEM_TIME AS OF a time stamp, FROM ... TO (closed-open) or BETWEEN ... AND (closed-closed). Without these clauses, a query returns current rows only.
• Updates and deletes can apply to part of a valid period (FOR PORTION OF).
• A bitemporal query combines both axes: for example, the department an employee was in on one date, as recorded in the database on another.

Temporal tables entered the SQL standard in SQL:2011. Products implement them to different extents, so the syntax available varies.

Changes and corrections

The distinction that bitemporal data exists to preserve:

• A change in the world (a new chief investment officer, a fund extension) closes one valid period and opens another.
• A correction of the record (the previous end date was wrong) adds a new version with the corrected valid period and closes the old version's transaction period.

A system with only valid time cannot tell these apart afterwards: a corrected period looks as if it had always been recorded that way. A system with only transaction time can show what changed in the record but not when the fact took effect.

Why it matters in private markets

• Explaining past decisions. What did the team know about a manager, its key people and its fund sizes on the date of the investment committee? That is an as-known question. See point-in-time data.
• Restated figures. NAVs, track records and fund sizes are restated; both the original and the restated values remain retrievable.
• Role and relation histories. Late-discovered departures and appointments can be back-dated in valid time without hiding when they were learned. See temporal validity.
• Audit. System-maintained history gives an audit trail that users cannot rewrite, one part of a value's data provenance.

Design and storage choices

• Deleting rows destroys transaction history; a fact recorded in error is closed in transaction time, not deleted.
• Letting users edit transaction time defeats the purpose.
• Far-future sentinels. SQL system-versioned tables set the end of a current row to the highest value of the column type. For valid time, a sentinel end encodes "no end observed"; it should not be read as a statement that the fact is still true.
• Storage and query cost grow with history; indexes and retention policies need to be planned, not added later.

How Altss applies this (Altss methodology)

Under Altss methodology, each version of a claim carries a valid-time interval and a transaction-time interval. Transaction time is append-only: when a claim changes, the new version is added and the old version's transaction interval is closed. A correction to a past value is recorded as a new version with the same valid time and a later transaction time; nothing is deleted or overwritten. A new version carries its own validation status while the earlier version keeps the status and date it held; each version's value keeps its derivation status, and each evidence item its evidence origin. Past states are retrieved in the current, as-was and as-known views. See the Temporal Data & Freshness Methodology.

Worked example

Examples are illustrative; figures are not market data.

Not the same as

• Temporal Validity: Temporal validity is the valid-time axis alone; bitemporal data adds transaction time, so a correction stays distinguishable from a change.
• Point-in-Time Data: Point-in-time data is the use case (reproducing what was known); bitemporal storage is one way to provide it.
• Data Provenance: Data provenance records where a value came from and how it was produced, including the audit trail of changes to the record; bitemporal data adds when each fact was true in the world.

Common mistakes

• Storing one date per row and calling it temporal.
• Treating a correction as if the world had changed, or a change as if the record had been wrong.
• Using the source capture date as the transaction time.
• Allowing users or batch jobs to rewrite system time stamps.
• Deleting erroneous rows instead of closing them.

Edge cases

• Retroactive facts: valid time starts before transaction time.
• Proactive facts: a future appointment is recorded now with a future valid start.
• Correcting only part of a valid period splits the row into pieces (FOR PORTION OF in SQL:2011).
• A fact recorded in error and then withdrawn remains visible in as-known views for the period it was held.

Questions

Sources

1. Developing Time-Oriented Database Applications in SQL. Richard T. Snodgrass, Morgan Kaufmann, Morgan Kaufmann Series in Data Management Systems; ISBN 1-55860-436-7. Status: Out of print; author-hosted PDF (checked 2026-10-01). Sec. 1.1 (kinds of time); ch. 2, sec. 2.3 (bitemporal tables) — supports: Valid time and transaction time; a table supporting both is bitemporal
2. Temporal features in SQL:2011. Krishna Kulkarni; Jan-Eike Michels, ACM SIGMOD Record, Vol. 41(3), pp. 34-43. Status: Published (checked 2026-10-01). Sections 2.1-2.4 (footnote 5 on the term "bitemporal") — supports: SQL:2011 period definitions, system-versioned and application-time period tables, AS OF / FROM ... TO / BETWEEN queries, bitemporal queries, tamper-resistant history

• Temporal Validity
• Point-in-Time Data
• Evidence Dates
• Data Provenance
• Data Freshness