Temporal Features

ProvSQL provides support for temporal databases – databases where data validity is associated with time intervals [Widiaatmaja et al., 2025]. This feature is implemented on top of the data-modification tracking infrastructure.

Note

Temporal features require PostgreSQL ≥ 14.

Overview

Temporal provenance allows you to track when each fact was valid, represent intervals of validity, and query the database “as of” a given point in time. The implementation uses the PostgreSQL tstzmultirange type to represent validity periods.

Temporal Tables

A temporal table is a provenance-enabled table augmented with a validity interval column. Helper functions are provided to create and manage such tables.

Valid-Time Queries

get_valid_time returns the validity interval of a fact as a tstzmultirange, computed from the provenance circuit and the modification history:

SELECT *, get_valid_time(provsql, 'mytable') AS valid_time
FROM mytable;

You can filter to only currently-valid facts:

SELECT * FROM mytable
WHERE get_valid_time(provsql, 'mytable') @> CURRENT_TIMESTAMP;

Union of Validity Intervals

union_tstzintervals computes the union of validity intervals associated with a query result via its provenance:

SELECT entity_id,
       union_tstzintervals(provenance(), 'interval_mapping')
FROM temporal_table;

Temporal Query Functions

ProvSQL provides additional functions for time-travel queries:

timetravel returns all versions of a table that were valid at a given point in time:

SELECT * FROM timetravel('mytable', CURRENT_TIMESTAMP)
  AS t(id int, value int, valid_time tstzmultirange, provsql uuid);

timeslice returns all versions valid during a given interval:

SELECT * FROM timeslice('mytable',
                        CURRENT_TIMESTAMP - INTERVAL '1 day',
                        CURRENT_TIMESTAMP)
  AS t(id int, value int, valid_time tstzmultirange, provsql uuid);

history returns the full modification history for a specific entity, identified by key column values:

SELECT * FROM history('mytable', ARRAY['id'], ARRAY['42'])
  AS t(id int, value int, valid_time tstzmultirange, provsql uuid);

Relationship to Data Modification Tracking

Temporal support is built on top of data modification tracking (see Data Modification Tracking). The provenance circuit records the full history of insertions and deletions, which is then interpreted temporally by the interval-aware evaluation functions.