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

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 a backward-compatible alias for sr_temporal retained for existing code; new code should use sr_temporal directly. See Semiring Evaluation for a description of the underlying interval-union m-semiring.

sr_temporal is the tstzmultirange specialisation of a more general interval-union m-semiring parameterised by the carrier type: union for ⊕, intersection for ⊗, and set difference for monus, defined uniformly over any densely-ordered linearly-ordered carrier with a bounded order. ProvSQL ships two further instances : sr_interval_num over nummultirange (e.g. measurement-validity ranges in scientific data integration) and sr_interval_int over int4multirange (e.g., page-range or line-range provenance in scholarly or source-code corpora). All three share the same algebra and the same C++ kernel (IntervalUnion(Oid)), differing only in the underlying multirange type. ProvSQL Studio surfaces them as a single Interval union (multirange) option in its evaluation strip, with the kernel selected automatically from the chosen mapping’s value type; see ProvSQL Studio.

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.