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.