Bitemporal Tables — Valid Time, Transaction Time & History Queries

Bitemporal tables track two time axes simultaneously:

Valid time — when the fact was true in the real world
Transaction time — when we recorded it in the database

This lets you answer both "what did we know on date X?" and "what was actually true on date X?" — even if we recorded it late or corrected it.

-- Bitemporal employee salary table
CREATE TABLE IF NOT EXISTS bt_salaries (
    id              BIGSERIAL PRIMARY KEY,
    employee_id     BIGINT NOT NULL,
    salary_usd      INT NOT NULL,

    -- Valid time: when was this salary actually in effect?
    valid_from      DATE NOT NULL,
    valid_to        DATE NOT NULL DEFAULT '9999-12-31',

    -- Transaction time: when did we record/correct this row?
    recorded_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    superseded_at   TIMESTAMPTZ,           -- NULL = current record

    CONSTRAINT valid_period_check CHECK (valid_from < valid_to)
);

CREATE INDEX IF NOT EXISTS idx_bt_salaries_employee
    ON bt_salaries (employee_id, valid_from, valid_to);

-- Reset for a clean re-run
TRUNCATE bt_salaries RESTART IDENTITY;

-- Insert initial salaries
INSERT INTO bt_salaries
    (employee_id, salary_usd, valid_from, valid_to, recorded_at)
VALUES
    (1, 80000, '2022-01-01', '9999-12-31', '2022-01-01'),
    (2, 95000, '2022-01-01', '9999-12-31', '2022-01-01'),
    (3, 72000, '2022-01-01', '9999-12-31', '2022-01-01');

-- Employee 1 gets a raise effective 2023-01-01 (we record it on that day)
-- Step 1: close the old record's valid_to
UPDATE bt_salaries
SET valid_to = '2023-01-01'
WHERE employee_id = 1 AND valid_to = '9999-12-31';

-- Step 2: insert the new salary period
INSERT INTO bt_salaries (employee_id, salary_usd, valid_from)
VALUES (1, 90000, '2023-01-01');

-- Late correction: we just discovered employee 2 had a raise in mid-2022
-- that was never recorded. We add it retroactively today.
-- Mark old records as superseded
UPDATE bt_salaries
SET superseded_at = NOW()
WHERE employee_id = 2 AND superseded_at IS NULL;

-- Insert corrected history
INSERT INTO bt_salaries
    (employee_id, salary_usd, valid_from, valid_to, recorded_at)
VALUES
    (2, 95000, '2022-01-01', '2022-07-01', NOW()),
    (2, 102000, '2022-07-01', '9999-12-31', NOW());

Purchase this course to unlock the full lesson.

← Previous Next →