Temporal Databases: Managing Time-Varying Data

The Problem with Timestamps

Most applications track time poorly. A common pattern: add created_at and updated_at columns, and call it done. When a record changes, you overwrite it. The previous value is gone.

This is fine until it isn't. Audits need to see what a record contained at a specific point in the past. Regulatory compliance demands a full change history. Bugs corrupted data and you need to know what was correct before 3 AM last Tuesday. Suddenly, updated_at is useless.

Temporal database modeling — particularly bitemporal modeling — is the systematic solution. It is more complex than simple timestamping, but it handles time correctly.

Two Dimensions of Time

Temporal data operates on two independent time axes:

Valid Time (aka Application Time): When a fact was true in the real world. An employee's salary increase was effective from April 1, even if you entered it on April 5.

Transaction Time (aka System Time): When the database knew about a fact. You recorded the salary change on April 5 at 14:32 UTC.

A bitemporal table tracks both axes. This lets you answer questions like: "As of our records on May 1 (transaction time), what was this employee's salary on March 15 (valid time)?"

Most teams only need valid time. Some — particularly in finance, healthcare, and legal domains — need both.

PostgreSQL: Valid Time with Ranges

PostgreSQL's tstzrange (timestamptz range) type is excellent for valid-time modeling:

CREATE TABLE employee_salary (
  id           BIGSERIAL PRIMARY KEY,
  employee_id  BIGINT NOT NULL,
  salary       NUMERIC(12,2) NOT NULL,
  valid_period tstzrange NOT NULL,
  CONSTRAINT no_overlap EXCLUDE USING gist (
    employee_id WITH =,
    valid_period WITH &&
  )
);

The EXCLUDE constraint prevents overlapping ranges for the same employee — a database-enforced invariant that would otherwise require application-level locking.

Insert a salary record:

INSERT INTO employee_salary (employee_id, salary, valid_period)
VALUES (
  42,
  95000.00,
  tstzrange('2026-01-01', '2026-06-01', '[)')
);

The [) notation means inclusive start, exclusive end — standard for half-open intervals.

Query what salary was valid on a given date:

SELECT salary
FROM employee_salary
WHERE employee_id = 42
  AND valid_period @> '2026-03-15'::timestamptz;

The @> operator means "contains this point." Clean, indexed, correct.

PostgreSQL 16: SQL/Temporal Standard

PostgreSQL 16 added native support for the SQL:2011 temporal table standard:

CREATE TABLE employee_salary (
  employee_id  BIGINT NOT NULL,
  salary       NUMERIC(12,2) NOT NULL,
  valid_from   DATE NOT NULL,
  valid_to     DATE NOT NULL,
  PERIOD FOR valid_time (valid_from, valid_to)
);

This enables period predicates in queries:

-- Find records valid on a specific date
SELECT * FROM employee_salary
FOR valid_time AS OF DATE '2026-03-15'
WHERE employee_id = 42;

Still limited compared to full bitemporal, but the standard syntax improves readability and portability.

Implementing System Time (Transaction Time)

For full bitemporal behavior, add system time columns and use triggers or application logic to maintain them:

CREATE TABLE employee_salary_bi (
  id              BIGSERIAL PRIMARY KEY,
  employee_id     BIGINT NOT NULL,
  salary          NUMERIC(12,2) NOT NULL,
  valid_from      TIMESTAMPTZ NOT NULL,
  valid_to        TIMESTAMPTZ NOT NULL DEFAULT 'infinity',
  sys_from        TIMESTAMPTZ NOT NULL DEFAULT now(),
  sys_to          TIMESTAMPTZ NOT NULL DEFAULT 'infinity'
);

When a record changes, you do not UPDATE. You close the current row (set sys_to = now()) and insert a new one:

-- "Closing" the current version
UPDATE employee_salary_bi
SET sys_to = now()
WHERE employee_id = 42
  AND sys_to = 'infinity'
  AND valid_from = '2026-01-01';

-- Inserting the corrected version
INSERT INTO employee_salary_bi
  (employee_id, salary, valid_from, valid_to, sys_from, sys_to)
VALUES
  (42, 98000.00, '2026-04-01', 'infinity', now(), 'infinity');

Now you can reconstruct any past state of the database:

-- What did we believe on May 1 about this employee's current salary?
SELECT salary, valid_from, valid_to
FROM employee_salary_bi
WHERE employee_id = 42
  AND sys_from <= '2026-05-01'
  AND sys_to > '2026-05-01'
  AND valid_from <= now()
  AND valid_to > now();

Temporal Patterns in Practice

Gap detection: Find periods where no record exists (coverage gaps):

WITH gaps AS (
  SELECT
    employee_id,
    valid_to AS gap_start,
    LEAD(valid_from) OVER (PARTITION BY employee_id ORDER BY valid_from) AS gap_end
  FROM employee_salary
  WHERE sys_to = 'infinity'
)
SELECT * FROM gaps
WHERE gap_start < gap_end;

Retroactive corrections: An employee's contract was backdated. Insert a record with a past valid_from and close the conflicting row:

-- Split existing record at the correction point
UPDATE employee_salary_bi
SET valid_to = '2026-03-01'
WHERE employee_id = 42
  AND valid_from = '2026-01-01'
  AND sys_to = 'infinity';

-- Insert corrected retroactive record
INSERT INTO employee_salary_bi
  (employee_id, salary, valid_from, valid_to)
VALUES
  (42, 92000.00, '2026-03-01', '2026-06-01');

The Tradeoffs

Temporal tables require more storage (you keep every version). Queries become more complex (every query adds time predicates). Updates become non-destructive multi-step operations.

In exchange, you get: complete audit trails with zero extra effort, the ability to rewind to any past state, and regulatory compliance without separate audit logging systems.

Use temporal modeling when your domain has change history requirements. Avoid it for ephemeral data (sessions, cache) where history has no value. And if you are on PostgreSQL, the range type support makes valid-time modeling surprisingly clean — no external libraries required.