Bitemporality
The question audits actually ask, and the reason one timestamp column cannot answer it.
The question is not "what is the value," and not even "what was the value on the 1st." It is: on the 15th, what did we believe about the 1st?
One timestamp cannot answer that, because it collapses two independent facts. Store only updated_at and you can reconstruct what the row said at any past moment but not what period of the world it was asserting. Store only effective_from and you get the world's timeline but not what you knew when you acted. Restatements live exactly in that gap.
SELECT ENo, EDept
FROM Emp FOR SYSTEM_TIME AS OF TIMESTAMP '2011-07-01 00:00:00'
WHERE ENo = 22217 AND EPeriod CONTAINS DATE '2010-12-01'The standard deliberately did not add a period data type, because that would have forced changes through every driver, ETL tool and replication path. A period is metadata over two ordinary columns, with closed-open semantics. FOR PORTION OF does interval surgery for you: an overlapping row extending beyond the target on both sides is split into three contiguous rows with only the middle updated.
Trigger semantics nobody expects: the updated row fires UPDATE, and the two fragments fire INSERT. And temporal uniqueness needs its own constraint, because the naive key allows an employee in two departments at once.
Where can you run bitemporal queries natively today?
The rest of this lesson unlocks when you commit above.