--- title: "Bitemporal Data | Altss Glossary" description: "Bitemporal data is a record of facts kept on two independent time axes: valid time, when each fact was true in the world, and transaction time, when the…" canonical: "https://altss.com/glossary/bitemporal-data" --- Glossary · Evidence & data # Bitemporal Data Also called: valid time and transaction time Bitemporal data is a record of facts kept on two independent time axes: valid time, when each fact was true in the world, and transaction time, when the database held that version of it. Publisher: Altss LLCContent modified 2026-10-01 ALTSS-DATA-013 A bitemporal record can answer "what did we believe on date K about date S?". When a fact changes in the world, a new valid period starts. When the record is corrected, a new version is added with the same valid period and a later transaction time. Nothing is overwritten, so the history of the world and the history of what was known are both kept. ### Formal definition Snodgrass defines valid time as when a fact was true in reality and transaction time as when it was stored in the database; a table supporting both is bitemporal. The 2011 edition of the Structured Query Language (SQL) standard, SQL:2011, allows one table to be both an application-time period table (valid time) and a system-versioned table (transaction time); the standard does not itself use the word "bitemporal", which comes from the literature. ## 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](https://altss.com/glossary/evidence-dates)) 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](https://altss.com/glossary/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](https://altss.com/glossary/temporal-validity). - **Audit.** System-maintained history gives an audit trail that users cannot rewrite, one part of a value's [data provenance](https://altss.com/glossary/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](https://altss.com/knowledge-center/frameworks/temporal-data-and-freshness-methodology). ## Worked example ### Illustrative chief investment officer change recorded late | Version | Holder | Valid time | Transaction time | | --- | --- | --- | --- | | v1 | Person A | [1 Jan 2019, end unknown) | [15 Jan 2019, 10 Sep 2024) | | v2 | Person A | [1 Jan 2019, 1 Jul 2024) | [10 Sep 2024, current) | | v3 | Person B | [1 Jul 2024, end unknown) | [10 Sep 2024, current) | On 10 September 2024 the plan's published minutes show that A left on 30 June and B started on 1 July. v1 is closed in transaction time and v2 and v3 are added. - "Who was CIO on 15 August 2024, as recorded on 1 September 2024?" returns **A**: that was the record then. - "Who was CIO on 15 August 2024, as recorded today?" returns **B**. Both answers are correct for their question, and both remain retrievable. Examples are illustrative; figures are not market data. ## Not the same as - [Temporal Validity](https://altss.com/glossary/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](https://altss.com/glossary/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](https://altss.com/glossary/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 ### Is a system-versioned table bitemporal? No. A system-versioned table keeps transaction time only. It becomes bitemporal when it also has an application-time (valid-time) period. ## Sources - [Developing Time-Oriented Database Applications in SQL](https://www2.cs.arizona.edu/~rts/tdbbook.pdf). 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 - [Temporal features in SQL:2011](https://doi.org/10.1145/2380776.2380786). 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 ## Related terms 5 terms - [Temporal Validity](https://altss.com/glossary/temporal-validity) - [Point-in-Time Data](https://altss.com/glossary/point-in-time-data) - [Evidence Dates](https://altss.com/glossary/evidence-dates) - [Data Provenance](https://altss.com/glossary/data-provenance) - [Data Freshness](https://altss.com/glossary/data-freshness) ## Concept record Concept ID ALTSS-DATA-013 Classification Evidence & data Topics Private markets data & OSINT Version 2.0.0 Last reviewed 2026-10-01 Structured data [JSON](https://altss.com/reference/concepts/bitemporal-data.json) ## Canonical URL https://altss.com/glossary/bitemporal-data