Temporal And Bitemporal Data
Ordinary tables store only the current state. Temporal modeling instead keeps history, so you can ask what a value was at a past moment. The key is that there are two independent notions of time.
- Valid time when a fact is true in the real world, such as the period a salary was in effect.
- Transaction time when the database recorded or knew the fact, such as when the row was inserted or corrected.
A table that tracks both is bitemporal. Each row carries a valid from and valid to plus a recorded from and recorded to. This separation answers two distinct questions. What was the salary on a given date, using valid time, versus what did the system believe the salary was as known on a given date, using transaction time.
Bitemporal data shines when corrections matter. Suppose a salary was wrong and you fix it. With valid time alone the mistake vanishes. With both times you can record that the salary was always meant to be one value, while still showing that the database believed the wrong value until the correction date. This is essential for audits, finance, and regulatory reporting where reproducing a past report exactly is required.
The price is complexity. Inserts often close out an old row and open a new one, queries filter on two time ranges, and uniqueness must respect periods rather than single points.
Key idea
Bitemporal data records both when a fact was true and when it was known, letting you reproduce past reports and represent retroactive corrections.