Temporal Database: Query Data as of Any Past Time

A temporal database is a database that stores each fact together with time periods, so an update closes the old row version instead of overwriting it, and a query can ask what the data was as of a past time T. The periods are usually valid time (when a fact is true in the real world) and transaction time (when the database recorded it); a table that keeps both is bitemporal.

This guide is for developers deciding whether to turn on system-versioned tables, keep history in application code, or store changes as events, because an audit, a bug report, or a data fix asks what the data looked like at a past time. It covers the time axes, the SQL:2011 temporal tables in SQL Server, MariaDB, IBM Db2, and PostgreSQL, how an AS OF query picks a row version, where temporal databases are used, and their costs. It does not teach full temporal SQL, and it does not compare vendors on speed. The example section shows a real run in which Loglune, a feature-flag platform, answers the same “as of T” question for feature flag versions.

What is a temporal database?

A temporal database attaches time to every stored fact and keeps the old versions of a fact when the fact changes. It stores data relating to time instances, covering past, present, and future time. A shorter working definition is a database with built-in time period and history functionality.

The difference from a regular table shows up on the first update. A John Doe example stores a person’s address in a table person(name, address). When John registers his move to Bigtown on December 27, 1994, the update overwrites “Smallville”, and the database can no longer answer where John lived before that date. A temporal version of the same table adds a period to each row. The Smallville row gets an end time, a new Bigtown row starts, and both rows stay queryable.

Before temporal features existed in the database engine, applications imitated them with soft deletes or slowly changing dimensions. A temporal database moves that bookkeeping from application code into the database.

A second example is an employee’s job title with the dates it applied. In an example, Tim Sehn is a Software Engineer Intern from January 1, 2020, a Software Engineer from January 1, 2021, and a Software Engineer Manager from January 1, 2022, and each title row also records when HR entered it. Stock price histories, sensor readings, and address histories are other temporal data.

Valid time, transaction time, and decision time

A temporal database can track up to three kinds of time for each fact, and each kind answers a different question.

Time axis	What it records	Question it answers	Who sets it
Valid time (application time)	When the fact is true in the real world	Where did John live in 1992?	The application or the user
Transaction time (system time)	When the database recorded the fact	In 1992, where did the database believe John lived?	The database engine
Decision time	When the decision about the fact was made	What was officially decided at a given time, even if it was entered later?	The application

The answers to the two example questions can differ, because the database may have been corrected after 1992. Valid time gives historical information, and transaction time gives rollback information.

An employee example puts all three on one record. A promotion is effective January 1, the promotion committee decides it on January 7, and HR enters it on January 14. The valid time starts January 1, the decision time January 7, and the transaction time January 14.

DoltHub also notes that valid time and decision time are ways to model some data, while transaction time applies to all data, which makes transaction time (system time) the most widely used temporal feature.

Uni-temporal, bitemporal, and tri-temporal databases

The number of time axes a table tracks gives the common labels.

• Uni-temporal: one axis, either valid time or transaction time. A table that records when each row version was written and nothing else is uni-temporal.
• Bitemporal: valid time and transaction time. A bitemporal table can show the corrected history and also what the database said before the correction. In an example, a tax investigation discovers on February 2, 2001 that John lived in Beachy from 1995 to 2000. The valid-time rows are corrected, and the transaction-time columns keep the record that the database once said Bigtown for those years.
• Tri-temporal: valid time, transaction time, and decision time. Add decision_from and decision_to columns next to the transaction-time columns to make a tritemporal table.

Most teams start with transaction time, because the database can fill it in without any change to application writes.

SQL:2011 temporal tables: system-versioned and application-time period tables

The SQL standard added temporal tables in SQL:2011. As of December 2011, ISO/IEC 9075 Part 2 defines three kinds:

1. Application-time period tables, which hold valid time in a PERIOD FOR declared on two user columns.
2. System-versioned tables, which hold transaction time that the database sets on every write.
3. System-versioned application-time period tables, which hold both and are bitemporal.

The standard grew out of earlier work. Richard Snodgrass proposed temporal extensions to SQL in 1992, and a committee developed them as TSQL2 in 1993. SQL:2011 adopted a different design from TSQL2.

How a system-versioned table works in SQL Server

Microsoft’s Temporal tables page (last updated August 18, 2026) describes the SQL Server implementation, which applies to SQL Server 2016 (13.x) and later and to Azure SQL Database. A system-versioned temporal table is a pair of tables: a current table with the current value of each row, and a history table with a mirrored schema that receives the previous version of a row on every update or delete. Two datetime2 period columns, typically named ValidFrom and ValidTo, hold each row version’s period. This is the example table from that page:

CREATE TABLE dbo.Employee
(
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR (100) NOT NULL,
    [Position] VARCHAR (100) NOT NULL,
    [Department] VARCHAR (100) NOT NULL,
    [Address] NVARCHAR (1024) NOT NULL,
    [AnnualSalary] DECIMAL (10, 2) NOT NULL,
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

On insert, SQL Server sets ValidFrom to the begin time of the current transaction in UTC and ValidTo to 9999-12-31, which marks the row as open. On update, SQL Server copies the old row to the history table with ValidTo set to the transaction’s begin time and writes the new value into the current table. On delete, the old row moves to the history table and leaves the current table. Microsoft notes that any data modification query adds a row to the history table, even if no column values change.

Other implementations

Product	Temporal support	Source
Microsoft SQL Server 2016 and later, Azure SQL Database	System-versioned temporal tables with a current table and a history table	Microsoft Learn
MariaDB 10.3.4 and later	SQL:2011 system-versioned tables (WITH SYSTEM VERSIONING), application-time periods, and bitemporal tables	MariaDB docs
IBM Db2 10	“Time travel query”, based on SQL:2011 temporal capabilities	IBM Documentation
Oracle Database	Oracle Workspace Manager for current, proposed, and historical versions of data	Oracle Workspace Manager
PostgreSQL	Range types since 9.2. PostgreSQL 18 (released September 25, 2025) adds temporal constraints: WITHOUT OVERLAPS on PRIMARY KEY and UNIQUE, and PERIOD on FOREIGN KEY. System versioning needs an extension (DoltHub lists temporal tables in Postgres as available via extension). Core PostgreSQL has no SQL:2011 system-versioned tables	PostgreSQL 18 release, DoltHub
MarkLogic 8.0 and later	Bitemporal documents with valid and system time stamps in JSON or XML	MarkLogic docs, MarkLogic blog
Dolt, TerminusDB	Version-controlled databases with time-travel queries and diffs	Dolt docs, TerminusDB on GitHub

DoltHub lists MySQL and SQLite as open source databases without temporal tables.

How do you query a temporal database as of a past time?

You query a past state with the FOR SYSTEM_TIME clause placed after the table name. FOR SYSTEM_TIME AS OF '2026-09-02 12:00' returns each row version whose period contains that time, which is the table as it was at that moment. SQL Server supports five subclauses, and its documentation gives the condition each one applies to the period columns:

Subclause	Rows returned	Condition on the period
AS OF date_time	Row versions current at that point in time	ValidFrom <= date_time AND ValidTo > date_time
FROM start TO end	Row versions active at any time in the range, excluding the upper boundary	ValidFrom < end AND ValidTo > start
BETWEEN start AND end	Same as FROM…TO, including rows that became active on the upper boundary	ValidFrom <= end AND ValidTo > start
CONTAINED IN (start, end)	Row versions opened and closed within the range	ValidFrom >= start AND ValidTo <= end
ALL	Every row version in the current and history tables	All rows

Microsoft’s own example asks which versions of employee 1000 were active during 2021:

SELECT *
FROM Employee FOR SYSTEM_TIME
    BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

MariaDB uses the same FOR SYSTEM_TIME clause with AS OF, BETWEEN, and FROM ... TO from SQL:2011 and adds ALL as a non-standard extension. The AS OF condition has a closed start and an open end, so a row version that ended exactly at T is excluded and the version that started at T is returned.

To compare two points in time, run the same query with AS OF T1 and AS OF T2 and compare the results, or use FROM T1 TO T2 to list every version that was active in between.

Example: reading a feature flag’s version as of T1 and T2

Feature flag configuration raises the same past-time question. When a customer reports that checkout failed on September 2, the first thing to establish is which flag rules were live on September 2, not which rules are live today. Loglune keeps every flag edit as a change event, a stored record of one edit, and its docs define the state at time T as the state rebuilt from the history up to T. Publishing a draft creates an immutable, numbered version with an optional publish note and an optional report ID, an identifier of the customer’s bug report that the version is attached to. The event sourcing guide covers how Loglune rebuilds state from events. This section looks at the as-of question itself.

The run below used Loglune’s shipped flag store code on a local Cloudflare D1 database in Vitest, on October 6, 2026. The flag new-checkout is on for customers whose plan is in a list. The data is an editorial example, not a customer case:

1. September 1, 2026, 09:00 UTC: an editor creates the flag for pro and enterprise. Version 1 is published at 10:00 with the note “Launch new checkout to pro and enterprise”.
2. September 3, 2026, 09:00 UTC: an editor narrows the list to enterprise. Version 2 is published at 10:00 with the note “Narrow to enterprise after payment errors” and report ID INC-1042.
3. September 4, 2026, 08:00 UTC: the on-call engineer adds a later note to version 2.

The run then asked for the version as of four times and diffed version 1 against version 2. This is the output, unedited:

publish version=1 publishedAt=2026-09-01T10:00:00.000Z
publish version=2 publishedAt=2026-09-03T10:00:00.000Z
--- change events: seq | recorded at | op | plans before -> after
1 | 2026-09-01T09:00:00.000Z | create | null -> ["pro","enterprise"]
2 | 2026-09-03T09:00:00.000Z | update | ["pro","enterprise"] -> ["enterprise"]
--- as-of-T projections
as of 2026-08-31T00:00:00.000Z: null (no version live)
as of 2026-09-02T12:00:00.000Z: version=1 publishedAt=2026-09-01T10:00:00.000Z plans=["pro","enterprise"] label="Launch new checkout to pro and enterprise" reportId=null laterNotes=[]
as of 2026-09-03T09:30:00.000Z: version=1 publishedAt=2026-09-01T10:00:00.000Z plans=["pro","enterprise"] label="Launch new checkout to pro and enterprise" reportId=null laterNotes=[]
as of 2026-09-04T12:00:00.000Z: version=2 publishedAt=2026-09-03T10:00:00.000Z plans=["enterprise"] label="Narrow to enterprise after payment errors" reportId="INC-1042" laterNotes=["2026-09-04T08:00:00.000Z oncall@example.com: Payment errors stopped after this version went live"]
--- diff version 1 -> version 2 (flag new-checkout)
flag new-checkout change=changed rules before=[{"id":"rule-plan","clauses":[{"attribute":"plan","op":"in","values":["pro","enterprise"]}],"variationId":"on"}]
flag new-checkout change=changed rules after=[{"id":"rule-plan","clauses":[{"attribute":"plan","op":"in","values":["enterprise"]}],"variationId":"on"}]
defaultVariationId before=off after=off
segments changed=0 findings=[]

Four results matter for a past-time investigation:

• Before the first version (August 31, 2026), the answer is null. No flag version was live, so there is nothing to reproduce from that time. Loglune’s docs map this case to a 404 for a reproduction request.
• As of September 2, 2026, version 1 was live with pro and enterprise, and its publish note travels with it.
• As of September 3, 2026, 09:30 UTC, the answer is still version 1. The narrowing edit had been recorded at 09:00, but version 2 was not published until 10:00. A query on the edit’s recorded time would have said ["enterprise"], while customers were still served ["pro","enterprise"]. This is the same gap that separates transaction time from valid time: the time a change was written down and the time it took effect are two different columns.
• As of September 4, 2026, version 2 is live, with its report ID INC-1042 and the on-call note added after publishing. The later note does not change version 2’s contents. Loglune’s docs define later notes as append-only additions that leave the version and its publish note unchanged.

The diff then answers what changed between T1 and T2 at field level: the plan clause went from ["pro","enterprise"] to ["enterprise"], the default variation stayed off, no segment changed, and the publish guard, the check Loglune runs when a version is published, had no findings for these two versions. The projection is the read-only view of the version live at T, and the diff compares two versions field by field.

Loglune does not offer a general-purpose temporal database or SQL FOR SYSTEM_TIME queries. It applies the as-of question to the flag versions it stores: the projection reads the version at T with its notes, and the diff compares two versions. Both are read-only and leave stored data and delivery unchanged.

What is a temporal database used for?

Temporal databases are used where the past state of data matters as much as the current one. Microsoft lists these use cases for temporal tables:

• Auditing all data changes and performing data forensics (see audit logging for the application-level alternative).
• Reconstructing the state of the data as of any time in the past.
• Calculating trends over time.
• Maintaining a slowly changing dimension for decision support applications.
• Recovering from accidental data changes and application errors.

MariaDB’s documentation adds legal requirements to keep data for N years and point-in-time recovery of a table. Domain examples include stock price histories in finance, current and past sensor readings in factory monitoring, patient histories in healthcare, and credit histories in banking.

For software teams, the common case is debugging and incident review. A customer report names a time (the situation behind reproducing a customer-specific bug), and the investigation needs the configuration and data as they were at that time, not as they are now.

Limits of temporal databases

Temporal tables add cost in storage, schema changes, and query design. Know these limits before you turn on system versioning:

• Storage grows with every write. DoltHub notes that history tables do not share storage between versions, so for wide schemas the history of every edit can be expensive. MariaDB stores history in the same table by default and suggests partitioning by SYSTEM_TIME to keep current-data scans fast, with DELETE HISTORY to prune old rows.
• Schema changes are hard. Temporal queries under an evolving schema are challenging, because a query over the history of an attribute may need rewriting for each schema version. In MariaDB, ALTER TABLE on a system-versioned table is an error by default, and with system_versioning_alter_history=KEEP the history shows the new table structure.
• Queries get more complex. Compared with non-temporal databases, query processing is slower and schema design is harder.
• Transaction time is not effect time. SQL Server records the begin time of the transaction in UTC, and MariaDB notes that a row inserted in a long transaction can become visible hours after its recorded time. If the question is when a change took effect, you need a column that records that time, which is a valid-time or application-time period.
• Backups may skip history. MariaDB’s mariadb-dump did not read historical rows before MariaDB 10.11, which added the --dump-history option.

FAQ

Is a temporal database the same as a time series database?

No. A time series database stores measurements indexed by time, such as a metric sampled every 10 seconds. A temporal database stores facts that change, and it keeps the periods during which each version of a fact was valid or recorded.

Is a temporal database related to Temporal, the workflow engine?

No. Temporal (temporal.io) is a workflow engine, and its persistence documentation lists Cassandra, PostgreSQL, and MySQL as the databases it stores workflow state in. A temporal database is the general database concept described in this guide.

How is a temporal database different from event sourcing?

A temporal database keeps row versions with time periods and lets you query a table as of T. Event sourcing stores every change as an event in an append-only log and rebuilds state by replaying the events. Both answer “what was the state at T”; the event sourcing guide covers the replay side in full.

Answering “what was live at T” for feature flags

A temporal database answers past-time questions by keeping every row version with its period, and the cost is storage, schema work, and careful choice of which time axis you query. Feature flags need the same answer during an incident: which version was live when the customer saw the problem, what note and report came with it, and what changed between that version and the next. Loglune keeps flag edits as change events, rebuilds the version at T from the history up to T, shows it in a read-only projection with its notes, and diffs any two versions with the publish guard’s findings, so the September 2 and September 4 states of a flag can be compared without changing what is served. Projections and diffs are read operations and do not count toward billing. A reproduction from time T, which builds a reproduction version from the state at T, counts as one reproduction, and the Free plan includes 5.