Temporal Features
==================

ProvSQL provides support for *temporal databases*, where data
validity is associated with time intervals
:cite:`DBLP:conf/pw/WidiaatmajaDDS25`.

.. 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 <https://www.postgresql.org/docs/current/rangetypes.html>`_
type.

Temporal Tables
---------------

A temporal table is simply a provenance-enabled table
(:sqlfunc:`add_provenance`) queried with data-modification tracking on
(``provsql.update_provenance = on``, see :doc:`data-modification`): 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 (:sqlfunc:`get_valid_time`, :sqlfunc:`timetravel`,
:sqlfunc:`timeslice`, :sqlfunc:`history`) query them.

Valid-Time Queries
------------------

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

.. code-block:: postgresql

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

You can filter to only currently-valid facts:

.. code-block:: postgresql

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

Union of Validity Intervals
-----------------------------

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

.. code-block:: postgresql

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

:sqlfunc:`union_tstzintervals` is an alias of :sqlfunc:`sr_temporal`.
The same interval-union m-semiring (see :doc:`semirings`) is also
available over ``nummultirange`` (:sqlfunc:`sr_interval_num`) and
``int4multirange`` (:sqlfunc:`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
:ref:`provsql-provenance-class`), including *temporal reachability*.
Over a graph whose edges carry validity multiranges, cyclic data
included, an ordinary recursive reachability query evaluates with
:sqlfunc:`sr_temporal` to exactly the set of instants at which each
vertex is reachable (on bounded-treewidth graphs; see
:doc:`probabilities`):

.. code-block:: postgresql

    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:

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

.. code-block:: postgresql

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

:sqlfunc:`timeslice` returns all versions valid during a given interval:

.. code-block:: postgresql

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

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

.. code-block:: postgresql

    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 :doc:`data-modification`). The provenance circuit records the full
history of insertions and deletions, which the functions above interpret
temporally.