Bi-Temporal Model

Most databases track one thing when you update a row: that it changed. Relata tracks two: when the fact was true in the world, and when the database learned about it. Those are different questions, and collapsing them into one timestamp causes subtle, hard-to-audit bugs in any system that ingests late-arriving data, corrects historical records, or must reproduce a past decision exactly.

Bi-temporality is not a layer on top of Relata — it is in the storage model, in the query planner, in the WAL, and in every Parquet snapshot.

The four timestamps

Every row in every type carries exactly four i64 nanosecond UTC timestamps:

TimestampAxisMeaning
valid_fromValid timeWhen the fact became true in the real world (inclusive)
valid_toValid timeWhen the fact stopped being true (exclusive)
system_fromSystem timeWhen the database recorded this version (inclusive)
system_toSystem timeWhen a later write superseded this version (exclusive)

Both intervals are half-open: [valid_from, valid_to) — a row is visible at valid_from inclusive and invisible at valid_to exclusive. An open valid_to or system_to is represented as i64::MAX.

Bi-temporal four-timestamp modelEach row version occupies a rectangle on a grid whose horizontal axis is valid time (when the fact was true) and vertical axis is system time (when the database recorded it). Row 1 spans valid-time Jun 1 onward and system-time Jun 3 to Jun 5; Row 2 spans the same valid time and system-time Jun 5 onward. AS OF slices vertically (a moment in valid time); AS OF SYSTEM TIME slices horizontally (a moment in system time); their intersection is the answer.valid_time → (when the fact was true)system_time → (when the DB recorded it)Jun 1Jun 1Jun 3Jun 3Jun 5Jun 5Row 1 — original entryRow 2 — corrected amountAS OF 'Jun 1'AS OF SYSTEM TIME 'Jun 4'

A concrete example

A bank processes a wire transfer on 1 June. The compliance team logs it on 3 June after a manual review. On 5 June an auditor corrects the amount.

Row 1 (original entry):
  valid_from   = 2024-06-01  (when the transfer happened)
  valid_to     = i64::MAX    (still "true" until corrected)
  system_from  = 2024-06-03  (when we logged it)
  system_to    = 2024-06-05  (superseded when corrected)

Row 2 (corrected amount):
  valid_from   = 2024-06-01  (same real-world date)
  valid_to     = i64::MAX
  system_from  = 2024-06-05  (when the correction was recorded)
  system_to    = i64::MAX    (current truth)

With a single-timestamp system, row 2 overwrites row 1 — the original belief is gone. With bi-temporality, both rows coexist. You can ask either question at any time.

Querying across time

Valid-time travel — what was true?

-- What was Alice's address on 1 January 2024?
SELECT name, address
FROM Person
AS OF '2024-01-01T00:00:00Z'
WHERE name = 'Alice'
 
-- What transactions were active on a given date?
SELECT * FROM Transaction
AS OF '2024-06-01T00:00:00Z'
WHERE amount > 10000

AS OF filters to rows where valid_from <= ts < valid_to.

AS OF in one line
AS OF answers "what was true at ts?" without touching system_*. It is the most common time-travel query — no WHERE predicates on timestamps needed.

System-time travel — what did we believe?

-- What did the database believe about Alice on 1 March 2024?
SELECT name, address
FROM Person
AS OF SYSTEM TIME '2024-03-01T00:00:00Z'
WHERE name = 'Alice'

AS OF SYSTEM TIME filters to rows where system_from <= ts < system_to.

Combining both axes

-- Given what we knew on 1 March, what should we have reported for 1 January?
-- Use explicit predicates to combine both axes:
SELECT name, address
FROM Person
WHERE valid_from  <= 1704067200000000000   -- 2024-01-01 in ns
  AND valid_to    >  1704067200000000000
  AND system_from <= 1709251200000000000   -- 2024-03-01 in ns
  AND system_to   >  1709251200000000000

Seeing the full history of a row

-- All versions of Alice's record, newest first
SELECT name, address, valid_from, valid_to, system_from, system_to
FROM Person
WHERE name = 'Alice'
ORDER BY system_from DESC

Without any AS OF, Relata returns the current state by default: the version whose valid_to = i64::MAX AND system_to = i64::MAX.

Timestamp format

The parser accepts two forms:

  • ISO-8601 UTC: '2024-06-01T00:00:00Z' or '2024-06-01'
  • Raw nanoseconds: 1717200000000000000
Warning
Explicit non-UTC offsets (e.g. '2024-06-01T00:00:00+05:30') are rejected at parse time. Convert to UTC before inserting — Relata will not silently shift your timestamps. This keeps cross-region audit chains unambiguous.

Writing bi-temporal data

On a plain INSERT, Relata sets system_from to the current HLC timestamp and valid_from to the same value unless you override it. To record a fact that was true in the past:

INSERT INTO Transaction (id, amount, valid_from, valid_to)
VALUES ('tx-42', 9800.00, '2024-06-01T00:00:00Z', '9999-12-31T00:00:00Z')

To correct a historical record, insert the corrected row with the same valid_from — the store creates a new system-time version automatically. The old version is retained and queryable.

Implementation notes

  • Row model and i64 timestamp type: relata-core
  • In-memory bi-temporal store with per-type interior locking: relata-storage
  • WAL and Parquet snapshots persist all four timestamps unchanged
  • HLC (Hybrid Logical Clock) keeps system_from monotonically increasing across restarts

What is not yet shipped

Note
SQL:2011 period predicates are not yet parsed: FOR SYSTEM_TIME FROM … TO …, OVERLAPS, CONTAINS, PRECEDES, and automatic period-splitting on UPDATE. Use explicit valid_from/system_from predicates or AS OF / AS OF SYSTEM TIME until they land.

See also

  • Provenance — every write also gets a hash-chained manifest entry
  • Governance — ACL and PURPOSE are recorded on the same system timeline
  • SQL Reference — full AS OF syntax