Cloud Parts

Bitemporal modeling

Record two timelines for every fact, when it was true in the world and when the database knew it, so you can correct the past without losing what you once believed.

1 technology on this page ↓

How it works

An ordinary table holds the current state, and an update overwrites it. A valid-time table (SQL:2011 calls it application time) gives each row a period, valid_from and valid_to, for when the fact is true in the real world, so a price or an address has a history and can even be set for the future. A transaction-time table (system time) adds a second period that the database fills in itself: when the row entered the database and when it was superseded. An update closes the old row instead of overwriting it, so you can read the table as it stood at any past moment. A bitemporal table has both, four timestamps per row. That separates a change (the customer moved on 15 March) from a correction (we had the date wrong), and answers questions like “what premium did we think applied on 10 March, on the day we sent the March invoice?”. Periods are closed-open, [from, to), and an open end is stored as a far-future value such as 9999-12-31. Nothing is updated in place: each change closes rows in system time and inserts new ones, so tables grow, primary keys need non-overlapping periods, and every query needs two time conditions, usually with system time defaulting to now.

Think of it as

A ship’s logbook kept in ink. It records where the ship was each day; when the navigator finds a mistake she doesn’t erase it, she adds a dated correction. Anyone can read the log as it stood on any day, or the best current account of any voyage.

Reach for it when

  • Insurance, payroll, banking and trading, where decisions made on old information must be explained later
  • Records you must reproduce exactly as reported on a past date, for auditors or regulators
  • Data that arrives late or gets corrected after the fact, such as back-dated trades or retroactive pay rises

Think twice when

  • You only need the current state, or a simple audit log of who changed what
  • The business forbids retroactive changes, so a change always takes effect when it’s recorded
  • The team can’t afford the extra complexity: every read, key and join has to think about two periods

A real example

An insurer records Mia’s car policy on 10 January at €40 a month from 1 January. On 20 March she reports that she moved on 1 March to a pricier postcode, so the premium is €52 from that date, and the March invoice sent on 25 March charges it. On 5 April she sends the removal firm’s receipt: the move was really on 15 March. Nobody edits rows. The old rows are closed in system time and corrected ones inserted. Asked today what the premium was on 10 March, the table says €40; asked what it believed on 25 March, it still says €52, which explains the invoice and the refund that follows.

Technologies

Specific products, and what each does differently.

Mia’s policy, row by row#

Valid time says when a premium applies; recorded (system) time says when the database held that belief. ∞ stands for 9999-12-31. Each period includes its start and excludes its end.

RowValid fromValid toRecorded fromRecorded toPremiumWhy the row exists
12026-01-01∞2026-01-102026-03-20€40Policy starts; closed when the move is reported
22026-01-012026-03-012026-03-202026-04-05€40Change: old premium until the move
32026-03-01∞2026-03-202026-04-05€52Change: new premium from the reported move date
42026-01-012026-03-152026-04-05∞€40Correction: the move was really on 15 March
52026-03-15∞2026-04-05∞€52Correction: new premium from the real date
  • Rows 1 to 3 are never deleted. Ending their recorded period is what keeps the old beliefs readable.
  • A table with only valid time would have rows 4 and 5 and no trace of the March invoice’s premium; a table with only system time couldn’t say the move happened before it was reported.

Asking the table questions#

Every question pins both timelines: pick the rows whose recorded period contains “known as of”, then the one whose valid period contains “valid at”.

QuestionValid atKnown as ofRowAnswer
Premium on 28 February, as the 25 February invoice saw it2026-02-282026-02-251€40
Premium on 10 March, as the 25 March invoice saw it2026-03-102026-03-253€52
Premium on 10 March, as we know today2026-03-10today4€40
Premium on 1 May, as we know today2026-05-01today5€52
  • The second and third rows disagree, and both are right: that difference is the refund owed for 1 to 14 March.

Where it’s built in#

SQL:2011 standardized both timelines, but support is uneven, and many teams still keep the four columns themselves.

IBM Db2

both

System-period, application-period and bitemporal tables. Db2 10 was the commercial implementation the authors of the SQL:2011 temporal features pointed to.

MariaDB

both

System-versioned tables since 10.3.4 (January 2018), application-time periods with FOR PORTION OF since 10.4.3 (February 2019), and the two combined as bitemporal tables. Today’s docs also cover WITHOUT OVERLAPS keys.

SQL Server and Azure SQL

system time

System-versioned temporal tables since SQL Server 2016: a current table plus a history table, queried with FOR SYSTEM_TIME. They are system-versioned only, so valid time is ordinary columns you manage yourself.

Oracle Database

both, differently

Flashback Query (AS OF) reads past states from undo data for as long as it’s retained, Flashback Time Travel archives history for longer, and Temporal Validity adds valid-time periods.

PostgreSQL

valid-time keys

PostgreSQL 18 added WITHOUT OVERLAPS primary keys and PERIOD foreign keys over range columns. SQL:2011 system-versioned and application-time period tables are still listed as unsupported.

XTDB and Datomic

built around time

XTDB (MPL-2.0; 2.0 in June 2025) keeps _valid_from, _valid_to, _system_from and _system_to on every row automatically. Datomic records transaction time: any database value can be read as-of a past transaction, since one, or as full history.

In code · sql

-- SQL:2011 bitemporal table, in MariaDB 10.4+ syntax (Db2 is similar)
CREATE TABLE policy (
  policy_id  INT,
  premium    DECIMAL(8,2),
  valid_from DATE,                                -- valid (application) time
  valid_to   DATE,
  row_start  TIMESTAMP(6) AS ROW START INVISIBLE,  -- system (transaction) time
  row_end    TIMESTAMP(6) AS ROW END INVISIBLE,
  PERIOD FOR valid_time (valid_from, valid_to),
  PERIOD FOR SYSTEM_TIME (row_start, row_end)
) WITH SYSTEM VERSIONING;

INSERT INTO policy (policy_id, premium, valid_from, valid_to)
VALUES (17, 40.00, '2026-01-01', '9999-12-31');

-- From 15 March the premium is 52: the row is split in valid time,
-- and the old version is kept in system time automatically
UPDATE policy FOR PORTION OF valid_time FROM '2026-03-15' TO '9999-12-31'
SET premium = 52.00
WHERE policy_id = 17;

-- What did we believe on 25 March about the premium on 10 March?
SELECT premium
FROM policy FOR SYSTEM_TIME AS OF TIMESTAMP '2026-03-25 00:00:00'
WHERE policy_id = 17
  AND valid_from <= '2026-03-10' AND valid_to > '2026-03-10';

Often confused with

Time-series databasesData warehousesBackup & restoreRelational databases
Where it came from1985–2025

In 1985 Richard Snodgrass and Ilsoo Ahn proposed “A Taxonomy of Time in Databases” to clear up confusion about the time attributes researchers kept adding. They separated transaction time (when a fact was stored) from valid time (when it was true in reality), plus user-defined time, and argued that future database systems should support them, since a system with only transaction time can roll back to an incorrect past state, while one with only valid time can’t show what it used to believe. A table with both came to be called bitemporal.

Snodgrass then pushed to standardize it. A committee he chaired, formed by email in July 1993, published the TSQL2 language specification in September 1994 and a book in 1995, and a temporal part of the SQL standard, SQL/Temporal, was started. In 1999 his book “Developing Time-Oriented Database Applications in SQL” showed how to build valid-time, transaction-time and bitemporal tables in plain SQL; it is now free to download from his site. The standards work met strong criticism: Hugh Darwen and C. J. Date argued that TSQL2’s hidden timestamp columns and statement modifiers broke the relational model, and SQL/Temporal was cancelled in 2001.

A different design, periods defined over ordinary pairs of columns, made it into SQL:2011, published in December 2011, with application-time period tables, system-versioned tables and the two combined. IBM Db2 10 was the first implementation its authors knew of; MariaDB, SQL Server (system time only) and others followed, and XTDB and Datomic built databases around time. In 2021 Martin Fowler’s “Bitemporal History” explained the idea with a payroll example using the friendlier terms “actual” and “record” time, and noted that the best approach is often to avoid bitemporality unless retroactive changes force it.

Why it’s built this way

Two independent time axes
Snodgrass and Ahn showed that when a fact is true and when it is recorded are orthogonal. Keeping both lets you fix the past (valid time) without rewriting what you once knew (transaction time).
Periods over pairs of ordinary columns, not a new data type
A PERIOD type would have to be added to every driver, stored-procedure language and ETL tool. SQL:2011 names a pair of start and end columns as a period instead, so existing tools keep working.
Closed-open periods with a far-future end
With [start, end) adjacent periods meet without overlapping or leaving gaps, so “current” is simply the row whose end is the maximum value, 9999-12-31 in practice.
The database owns system time
For audit and compliance, users must not be able to edit history. In system-versioned tables the database sets the timestamps and only current rows can change, so the recorded history can’t be tampered with.

Timeline

  1. 1985

    Snodgrass and Ahn’s “A Taxonomy of Time in Databases” separates valid and transaction time

  2. 1994

    The TSQL2 temporal extension to SQL-92 is published

  3. 1999

    Snodgrass publishes “Developing Time-Oriented Database Applications in SQL”

  4. 2001

    The SQL/Temporal standards project is cancelled amid controversy

  5. 2011

    SQL:2011 adds application-time periods and system-versioned tables

  6. 2018

    MariaDB 10.3.4 adds system-versioned tables

  7. 2021

    Martin Fowler publishes “Bitemporal History”

  8. 2025

    XTDB 2.0 ships bitemporal SQL; PostgreSQL 18 adds temporal keys

Sources17

Improve this page on GitHub