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 a provenance-enabled table augmented with a validity
interval column. Helper functions are provided to create and manage such
tables.

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`.

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.