Explain the difference between valid time — when a fact was true in the world — and system or transaction time — when the database recorded it. When does a design genuinely need both?

## Two independent clocks Every fact in a database has two lifetimes that people routinely conflate. **Valid time** (also *application time*, *effective time*, *business time*) is the interval during which the fact is true of the world. An employee's salary is €70,000 *from 1 March to 30 June*. Valid time is asserted by humans and business processes. It can be **backdated** (we learn on the 10th about something that started on the 1st) and **future-dated** (a contract that begins next quarter). **System time** (also *transaction time*) is the interval during which the database held that assertion. It is stamped by the engine at commit, is monotonically increasing, and can never be in the future or be altered retroactively — that immutability is precisely what makes it usable as an audit record. A table with only system time tells you what the database said and when it changed its mind, but not when facts were actually true. A table with only valid time tells you what is true and was true, but rewrites the past silently: after a backdated correction, no query can reproduce what a report generated last week would have shown. ## The example that makes it click Alice's salary rises to €70,000 effective **1 March**. HR enters it on **10 March**. On **20 March** someone notices it should have been €72,000, and corrects it — still effective 1 March. - Valid-time view: from 1 March, the salary is €72,000. (After the correction, the earlier €70,000 assertion is no longer part of the truth.) - System-time view: until 10 March the database said €65,000; from 10 to 20 March it said €70,000; since 20 March it says €72,000. Now ask the question an auditor asks: *the payslip we issued on 15 March — was it right?* Valid time alone says the salary was €72,000 on 15 March, so the payslip looks wrong. System time alone says the database held €70,000 on 15 March but cannot tell you the period that applied to. Only the two together reconstruct: *as of 15 March, we believed that from 1 March the salary was €70,000* — so the payslip was consistent with what we knew, and the €2,000 difference is retro-pay, not an error. ## The bitemporal model A bitemporal row carries two period pairs: ``` employee_salary ( employee_id, salary, valid_from, valid_to, -- when the fact holds sys_from, sys_to -- when we asserted it ) ``` Every row is a rectangle in a two-dimensional time space. Corrections never update rows in place: you close the current assertion's `sys_to` and insert new rows describing the corrected valid-time picture. The table is append-only in system time, which is what makes historical queries reproducible. Query patterns: - **Current truth**: `valid_from <= now < valid_to AND sys_to = infinity`. - **Historical truth as we now know it**: `valid_from <= :t < valid_to AND sys_to = infinity`. - **What we believed then, about then**: `valid_from <= :t < valid_to AND sys_from <= :asof < sys_to` — the reproducible-report query. The last one is the whole justification for the design. If nobody will ever ask it, you are paying for nothing. ## Where it is genuinely required - **Finance and accounting.** Restatements, closed-period adjustments, and the obligation to reproduce a filed report exactly as filed. - **Insurance.** Policies and endorsements routinely take effect before they are recorded; claims are assessed against the coverage that applied at the incident date, as understood at the assessment date. - **Payroll and HR.** Retroactive pay changes, backdated promotions, corrections to time records. - **Regulatory and risk reporting.** "Show us the position you reported on this date and how you computed it." - **Anything with a dispute process**, where you must show both what happened and what you knew when you acted. ## What it costs - **Query complexity.** Four temporal predicates on every access, plus correct handling of the open-ended sentinels. Wrap the common shapes in views or generated repository methods, because hand-writing them is where bugs live. - **Constraint complexity.** "One salary per employee at any instant" now means non-overlap in valid time *within* each system-time slice. Engines with `WITHOUT OVERLAPS` primary keys help; otherwise it is triggers and discipline. Temporal foreign keys — the referenced entity must exist for the whole referencing period — are harder still. - **Volume.** A single backdated correction can produce several rows as intervals are split. Tables grow faster than people estimate. - **Cognitive load.** Most bugs in bitemporal systems come from a developer reasoning about one axis while the data has two. This is the reason to apply it only to the tables that need it. ## Alternatives An **append-only event log** plus projections gives similar power: events carry both an occurrence time and a recorded time, and you rebuild any view at any pair of instants. It moves the complexity from schema to replay machinery and is a natural fit if the system is already event-driven. A cheaper middle ground covers many real requirements: effective-dated (valid-time) rows for the business truth, plus a plain append-only audit table capturing every change with its timestamp and actor. You can reconstruct what was believed when by replaying the audit table — clumsier than a bitemporal query, but far simpler day to day, and enough when "reproduce the exact report" is a rare forensic exercise rather than a routine one. The senior-level judgment is to identify which tables carry facts that get corrected retroactively *and* are relied upon by published outputs. Those tables — usually a small minority — get the full treatment; the rest get system-time auditing and nothing more.