Bi-Temporal Model
Bi-temporality is in the query path in Relata, not bolted on top. Every row carries four i64 nanosecond UTC timestamps, and the planner honours them on every read.
| Timestamp | Meaning |
|---|---|
valid_from / valid_to | When the fact was true in the real world ([valid_from, valid_to)) |
system_from / system_to | When the database knew about it ([system_from, system_to)) |
Why two time axes?
Real-world data is messy. A birth date may be corrected years later; a transaction may be reported days after it happened. With a single time axis you have to choose between “what was true” and “when did we know it”. Bi-temporality gives you both.
- Valid time answers: what was Alice’s address on 1 January 2024?
- System time answers: what did we believe Alice’s address was on 1 March 2024?
The intersection answers questions like: given what we knew on 1 March, what should we have believed about 1 January?
AS OF — point-in-time reads
-- Valid-time travel: what was true at this point in real-world time?
SELECT * FROM Person
AS OF '2024-06-01T00:00:00Z'
LIMIT 10
-- System-time travel: what did the database believe at this point?
SELECT * FROM Person
AS OF SYSTEM TIME '2024-06-01T00:00:00Z'
LIMIT 10AS OF returns rows whose valid_from ≤ ts < valid_to at the given timestamp.
Timestamps are i64 nanoseconds UTC
Internally every timestamp is i64 nanoseconds UTC. The parser accepts:
- Decimal nanoseconds (e.g.
1798765432100000000) - UTC ISO-8601 (
YYYY-MM-DD[THH:MM:SS[.fff][Z]]) - Quoted or bare
Explicit non-UTC offsets are rejected — convert to UTC first.
Implementation
- The bi-temporal row model lives in
relata-core. - The in-memory bi-temporal store with per-type interior locking is
relata-storage(#774 —&selfwrites). - All four timestamps are persisted to the WAL and Parquet snapshots.
- Bi-temporal conformance is verified by the suite
docs/src/guides/bitemporal-conformance.md.
What’s not yet parsed
The SQL:2011 period predicates family is not parsed (FOR SYSTEM_TIME FROM/TO/BETWEEN, OVERLAPS, CONTAINS, PRECEDES, auto period-splitting). Use explicit valid_from/system_from predicates, or AS OF for point-in-time (#969).