Temporal Features

ProvSQL provides support for temporal databases, where data validity is associated with time intervals [Widiaatmaja et al., 2025].

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. Validity periods are values of the PostgreSQL tstzmultirange type.

Temporal Tables

A temporal table is simply a provenance-enabled table (add_provenance) queried with data-modification tracking on (provsql.update_provenance = on, see Data Modification Tracking): no validity column is added to the user table. Validity ranges live in the provsql.update_provenance table (its valid_time column, a tstzmultirange), maintained automatically; the functions below (get_valid_time, timetravel, timeslice, history) query them.

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

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

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

union_tstzintervals is an alias of sr_temporal. The same interval-union m-semiring (see Semiring Evaluation) is also available over nummultirange (sr_interval_num) and int4multirange (sr_interval_int), e.g., for measurement-validity or page ranges.

The interval-union semiring is absorptive, so it also evaluates recursive queries under the 'absorptive' provenance class (see provsql.provenance (default: 'semiring')), including temporal reachability. Over a graph whose edges carry validity multiranges, cyclic data included, an ordinary recursive reachability query evaluates with sr_temporal to exactly the set of instants at which each vertex is reachable (on bounded-treewidth graphs; see Probabilities):

SET provsql.provenance = 'absorptive';
WITH RECURSIVE reach(node) AS (
    SELECT 1
  UNION
    SELECT e.dst FROM link e JOIN reach r ON e.src = r.node
)
SELECT node, sr_temporal(provenance(), 'validity_mapping')
FROM reach;

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 the functions above interpret temporally.