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). The provenance circuit records the full history of insertions and deletions, which is then interpreted temporally by the interval-aware evaluation functions.