0Pricing
SQL Academy · Lesson

Temporal and Versioned Rows

Valid-time and as-of queries.

Temporal and Versioned Rows is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Are Temporal Tables?

Temporal tables let you track how data changes over time. Instead of overwriting a row when something changes, a temporal table keeps every version of that row, each tagged with the time period it was valid.

There are two key concepts: valid time (when the fact was true in the real world) and transaction time (when the database recorded the fact). Combining both gives you a fully bi-temporal table.

Valid Time vs. Transaction Time

Valid time represents when a fact is true in the real world — for example, an employee's salary from 2020-01-01 to 2022-06-30. Transaction time is when the database row was inserted or expired. Together they answer two questions: What was true? and When did we know it?

Most practical use cases start with valid-time tracking, which you can implement manually using valid_from and valid_to columns.

Creating a Valid-Time Table

The simplest way to store versioned rows is to add valid_from and valid_to timestamp columns. A valid_to of NULL (or a far-future sentinel like 9999-12-31) means the row is currently active.

CREATE TABLE employee_salary (
  id          SERIAL PRIMARY KEY,
  employee_id INT NOT NULL,
  salary      NUMERIC(12, 2) NOT NULL,
  valid_from  DATE NOT NULL,
  valid_to    DATE
);

INSERT INTO employee_salary (employee_id, salary, valid_from, valid_to)
VALUES
  (1, 50000, '2020-01-01', '2022-06-30'),
  (1, 60000, '2022-07-01', NULL);

Querying the Current Version

To find the currently active row for each employee, filter for rows where valid_to IS NULL (open-ended) or where today's date falls within the valid range. Using a sentinel value like '9999-12-31' simplifies range comparisons.

SELECT employee_id, salary
FROM employee_salary
WHERE valid_to IS NULL
ORDER BY employee_id;

As-Of Queries

An as-of query asks: What was the data at a specific point in time? You filter rows where the given timestamp falls inside the valid window. This is one of the most powerful features of temporal tables.

-- What was employee 1's salary on 2021-03-15?
SELECT employee_id, salary, valid_from, valid_to
FROM employee_salary
WHERE employee_id = 1
  AND valid_from <= '2021-03-15'
  AND (valid_to IS NULL OR valid_to > '2021-03-15');

Updating a Versioned Row

When a fact changes, you do not UPDATE the existing row in place. Instead, you close the current row by setting its valid_to, and INSERT a new row with the new value. This preserves the full history.

-- Employee 1 gets a raise effective 2023-01-01
BEGIN;

-- Close the current open row
UPDATE employee_salary
SET valid_to = '2022-12-31'
WHERE employee_id = 1
  AND valid_to IS NULL;

-- Insert the new version
INSERT INTO employee_salary (employee_id, salary, valid_from, valid_to)
VALUES (1, 72000, '2023-01-01', NULL);

COMMIT;

Using daterange for Validity Periods

PostgreSQL's daterange type elegantly models a validity period as a single column. You can use the @> (contains) operator to check if a date falls within the range, and add an exclusion constraint to prevent overlapping periods for the same entity.

CREATE TABLE employee_salary_v2 (
  id          SERIAL PRIMARY KEY,
  employee_id INT NOT NULL,
  salary      NUMERIC(12, 2) NOT NULL,
  valid_period DATERANGE NOT NULL,
  EXCLUDE USING GIST (employee_id WITH =, valid_period WITH &&)
);

INSERT INTO employee_salary_v2 (employee_id, salary, valid_period)
VALUES
  (1, 50000, '[2020-01-01, 2022-07-01)'),
  (1, 60000, '[2022-07-01, infinity)');

As-Of Query with daterange

With the daterange approach, the as-of query becomes very readable. The @> operator checks that the given date is contained within the range, handling the lower and upper bounds automatically.

-- What was employee 1's salary on 2021-03-15?
SELECT employee_id, salary, valid_period
FROM employee_salary_v2
WHERE employee_id = 1
  AND valid_period @> '2021-03-15'::date;

System-Versioned Tables (SQL Standard)

The SQL:2011 standard introduced system-versioned temporal tables. The database automatically manages row_start and row_end transaction-time columns. In PostgreSQL you simulate this; in SQL Server and MariaDB it is built-in with SYSTEM VERSIONING.

The example below shows the SQL Server / MariaDB syntax as a reference for the concept.

-- SQL Server / MariaDB syntax (reference)
CREATE TABLE dbo.Product (
  ProductID   INT PRIMARY KEY,
  Name        VARCHAR(100),
  Price       DECIMAL(10,2),
  SysStart    DATETIME2 GENERATED ALWAYS AS ROW START,
  SysEnd      DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (SysStart, SysEnd)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Product_History));

Temporal Joins: Aligning Two Tables in Time

A common challenge is joining two temporal tables on matching time periods. For example, joining employee salaries to department assignments where both have valid-time periods. You join on entity key AND overlap condition using && on ranges or explicit date comparisons.

CREATE TABLE dept_assignment (
  employee_id INT,
  department  VARCHAR(50),
  valid_period DATERANGE
);

INSERT INTO dept_assignment VALUES
  (1, 'Engineering', '[2020-01-01, infinity)'),
  (1, 'Marketing',   '[2019-01-01, 2020-01-01)');

-- Periods where employee 1 was in Engineering AND had salary > 55000
SELECT s.salary, d.department,
       s.valid_period * d.valid_period AS overlap_period
FROM employee_salary_v2 s
JOIN dept_assignment d
  ON s.employee_id = d.employee_id
  AND s.valid_period && d.valid_period
WHERE s.employee_id = 1
  AND s.salary > 55000;

Preventing Gaps and Overlaps

Two common data quality problems in temporal tables are gaps (periods with no record) and overlaps (two rows that are simultaneously valid). The exclusion constraint with && prevents overlaps at the database level. Detecting gaps requires checking for missing coverage with a query.

-- Find gaps in salary history for employee 1
-- (periods where upper(prev) < lower(next))
SELECT
  upper(a.valid_period) AS gap_start,
  lower(b.valid_period) AS gap_end
FROM employee_salary_v2 a
JOIN employee_salary_v2 b
  ON a.employee_id = b.employee_id
  AND upper(a.valid_period) < lower(b.valid_period)
WHERE a.employee_id = 1
  AND NOT EXISTS (
    SELECT 1 FROM employee_salary_v2 c
    WHERE c.employee_id = 1
      AND lower(c.valid_period) > upper(a.valid_period)
      AND lower(c.valid_period) < lower(b.valid_period)
  )
ORDER BY gap_start;

Knowledge Check

Test your understanding of temporal tables and as-of queries.

Recap: Temporal and Versioned Rows

In this lesson you learned how to model time-varying data using valid-time columns and PostgreSQL's daterange type. Key takeaways:

  • Never overwrite historical rows — close the old one and insert a new version.
  • Use as-of queries (valid_from <= target AND valid_to > target) to retrieve data at any past moment.
  • The daterange type with the @> operator makes temporal queries concise and readable.
  • Exclusion constraints on && (range overlap) enforce data integrity at the database level.
  • Temporal joins align two histories by intersecting their valid periods.

These patterns form the foundation of event sourcing, audit logging, and any system where historical accuracy matters.

Frequently asked questions

Is the “Temporal and Versioned Rows” lesson free?

Yes — the full text of “Temporal and Versioned Rows” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Temporal and Versioned Rows”?

Valid-time and as-of queries. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Temporal and Versioned Rows” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Why Keep History
  2. Append-Only Event Tables
  3. Temporal and Versioned Rows
  4. Rebuilding State from Events
← Back to SQL Academy