Bitemporal table in SQLite or Postgres

Measure your machineNeeds Python

Problem

Two different times describe a fact: when it is true in the world (valid time) and when the database believed it (transaction time). A correction changes what we believe about the past without changing the past, so answering "what did we report on 10 March?" needs both. A table with a single updated_at column overwrites the old belief and can no longer answer it.

Create the rate table with valid_from, valid_to, tx_from, tx_to. Insert Ravi's ₹20, then add the correction by closing the old row's tx_to and inserting the ₹25 row. Run the two queries from Bitemporal data and joins and confirm 20 and 25. Then try to answer the same question using a single updated_at column.

The question. Can each design say what rate we believed on 10 March for 5 March, and what we believe now?

Predict

With only an updated_at column, can the table say what rate we believed on 10 March for 5 March?

YesNo: the old value is gone

Run it

1Install uv once. It fetches Python and the lab's packages for you.
Terminal
```
curl -LsSf https://astral.sh/uv/install.sh | sh
```

2Get the script. It is the whole lab: read it, then download it.

bitemporal-table-in-sqlite-or-postgres.py40 linesDownload

View the code

bitemporal-table-in-sqlite-or-postgres.py

# /// script
# requires-python = ">=3.10"
# ///
# Copyright (c) 2026 Aayush Sharma · SPDX-License-Identifier: MIT
# https://thedataengineering.com/lab-code/LICENSE.txt
"""A bitemporal table in SQLite.

A rate table with valid_from/valid_to (when the rate applies) and
tx_from/tx_to (when the database believed it). Ravi's rate of 20 is recorded
on 1 March, then corrected on 15 March to 25, valid from 1 March, by closing
the old row's tx_to and inserting the new row. Two questions: what did we
believe on 10 March about 5 March, and what do we believe now? Then the same
history kept with a single updated_at column.
"""
import argparse, json, sqlite3

p = argparse.ArgumentParser(description=__doc__.splitlines()[0])
p.add_argument("--json", help="Also write the results to this JSON file.")
args = p.parse_args()

c = sqlite3.connect(":memory:")
c.execute("CREATE TABLE rate (person TEXT, rate INT, valid_from TEXT, valid_to TEXT, tx_from TEXT, tx_to TEXT)")
c.execute("INSERT INTO rate VALUES ('Ravi', 20, '2025-03-01', '9999-12-31', '2025-03-01', '9999-12-31')")
c.execute("UPDATE rate SET tx_to = '2025-03-15' WHERE person = 'Ravi' AND tx_to = '9999-12-31'")        # the correction closes the old belief
c.execute("INSERT INTO rate VALUES ('Ravi', 25, '2025-03-01', '9999-12-31', '2025-03-15', '9999-12-31')")
AS_OF = "SELECT rate FROM rate WHERE person = 'Ravi' AND valid_from <= ? AND ? < valid_to AND tx_from <= ? AND ? < tx_to"
believed = c.execute(AS_OF, ("2025-03-05", "2025-03-05", "2025-03-10", "2025-03-10")).fetchone()[0]
now = c.execute(AS_OF, ("2025-03-05", "2025-03-05", "2025-03-20", "2025-03-20")).fetchone()[0]
u = sqlite3.connect(":memory:"); u.execute("CREATE TABLE rate (person TEXT, rate INT, updated_at TEXT)")
u.execute("INSERT INTO rate VALUES ('Ravi', 20, '2025-03-01')"); u.execute("UPDATE rate SET rate = 25, updated_at = '2025-03-15' WHERE person = 'Ravi'")
single = u.execute("SELECT rate, updated_at FROM rate").fetchall()
rows = [{"question": "rate for 5 March, as believed on 10 March", "bitemporal": believed, "updated_at_only": "cannot answer: the 20 is gone"},
        {"question": "rate for 5 March, as believed now", "bitemporal": now, "updated_at_only": str(single[0][0])}]
for r in rows: print(f"{r['question']:<44} bitemporal: {r['bitemporal']}   single updated_at: {r['updated_at_only']}")
print("the single-column table holds:", single)

if args.json:
    with open(args.json, "w") as f:
        json.dump({"tables": {"questions": rows}}, f)

3Run it in the folder you saved it to, with the defaults first. Then change a knob from the table below and run it again.

Terminal

uv run bitemporal-table-in-sqlite-or-postgres.py
uv run bitemporal-table-in-sqlite-or-postgres.py --help     # every knob

Our run

Two questions, two designs

Two questions, two designs
Question	Bitemporal	Single updated_at
rate for 5 March, as believed on 10 March	20	cannot answer: the 20 is gone
rate for 5 March, as believed now	25	25

Reference runApple M2 · 8 cores · 8 GB

Output · uv run bitemporal-table-in-sqlite-or-postgres.py

rate for 5 March, as believed on 10 March    bitemporal: 20   single updated_at: cannot answer: the 20 is gone
rate for 5 March, as believed now            bitemporal: 25   single updated_at: 25
the single-column table holds: [(25, '2025-03-15')]

macOS 26.6.2 · Python 3.13.3 · run 2026-10-20

What we found

The bitemporal table answered 20 for what we believed on 10 March and 25 for what we believe now. The single-column table could answer only the second; for the first: cannot answer: the 20 is gone.

Read it

If anyone will ever ask "what did we know then" (an audit, a restated report, a model trained on what was available at the time), the belief history must be kept; an update-in-place table destroys it on every correction. Transaction time is cheap to add at design time and impossible to reconstruct afterwards.