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

ProvSQL provides support for *temporal databases* – databases where data
validity is associated with time intervals
:cite:`DBLP:conf/pw/WidiaatmajaDDS25`. 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 <https://www.postgresql.org/docs/current/rangetypes.html>`_
type to represent validity periods.

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 by the data-modification
machinery; 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 a backward-compatible alias for
:sqlfunc:`sr_temporal` retained for existing code; new code should
use :sqlfunc:`sr_temporal` directly. See :doc:`semirings` for a
description of the underlying interval-union m-semiring.

:sqlfunc:`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 :
:sqlfunc:`sr_interval_num` over ``nummultirange`` (e.g.
measurement-validity ranges in scientific data integration) and
:sqlfunc:`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 :doc:`studio`.

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, an ordinary
recursive reachability query (compiled along a tree decomposition of
the data on bounded-treewidth graphs, cyclic data included; see
:doc:`probabilities`) evaluates with :sqlfunc:`sr_temporal` to
exactly the set of instants at which each vertex is reachable:

.. 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 is then interpreted temporally
by the interval-aware evaluation functions.
