Key takeaway
What applied on a date and what the system recorded about that date are different questions. A correction can change one without erasing the other.
Write the two questions before selecting a date
A reviewer asks which classification applied on February 15. Another asks what classification the system would have returned on February 20. Those questions can produce different answers after a backdated correction. A single last_modified column cannot express every distinction between the effective period and the period during which a version was recorded.
MariaDB’s bitemporal documentation combines application-time periods with system-versioning. The two axes have separate jobs. Application time describes the business period represented by the row; system history describes stored versions under the engine’s rules. The archive owner must explain what each source field actually records instead of assuming that a column named valid means business-effective time.
Keep the earlier answer after the correction
This hypothetical history concerns an invented operational code, not a price or a real client. On January 10, the system records code A as effective from January 1 onward. On March 3, an authorized correction says code B should have applied from February 1. The latest business view now splits into A for January and B from February.
The original recorded version remains part of the history. For the business date February 15, a query bounded by recorded time February 20 returns A in this fixture. The same business date viewed after the March 3 correction returns B. Neither result should be presented without its two time coordinates.
The table uses inclusive starts and exclusive ends. That is an explicit illustration convention, not a claim that every source query uses identical boundary operators. The correction reason and authority reference belong beside the version relationship; the fact that a database stored a change does not establish why the correction was justified.
| Recorded interval | Business-effective interval | Code | Interpretation |
|---|---|---|---|
| January 10 to March 3 | January 1 onward | A | Earlier stored understanding |
| March 3 onward | January 1 to February 1 | A | Corrected January segment |
| March 3 onward | February 1 onward | B | Backdated corrected segment |
Do not promote system time into human knowledge
SQL Server’s system-versioned temporal tables retain current and historical row versions. Its documentation says the system period uses the transaction’s begin time, in UTC based on the system clock. That is not automatically the commit time, the event’s occurrence time or the first moment a person learned the fact. Even familiar names such as ValidFrom and ValidTo need that implementation context.
The same documentation notes that FOR SYSTEM_TIME filters zero-duration versions produced by multiple updates to the same key within one transaction; inspecting those versions can require querying history directly. A convenient as-of result should not be advertised as every intermediate change unless its coverage was actually established.
For a receiving contract, identify the source engine/version, period semantics, capture behavior, retention and query convention. Keep a separately evidenced first-availability field if the evaluation requires it. Database version time is useful provenance, but it cannot retroactively supply a missing human decision record.
Test both dimensions at the boundary
Use the hypothetical ledger to ask four precise queries: business date January 20 as recorded February 20; business date February 15 as recorded February 20; then both business dates as recorded March 4. The expected answers are A, A, A and B. Check the actual receiving implementation against those declared results.
Test the exact February 1 and March 3 boundaries too. State timezone, date-versus-instant types and endpoint inclusion before running the query. A row ending at a boundary should not unexpectedly overlap its successor or disappear because two systems applied different conventions.
If the source contains only the final corrected row B, the receiver cannot manufacture the earlier A version from a latest-value export. Retain that limitation. The package may support the latest corrected classification while being unsuitable for a claim about the earlier stored view. That is a useful scope decision, rather than a reason to invent change timestamps.
Attach the answer to its history recipe
The completed record contains the entity key, business interval, recorded interval, source/version rule, superseded-version link, correction reason, query boundary and expected fixture answers. A result report should identify which pair of dates it used. Calling either answer “the February value” conceals the distinction the review was meant to preserve.
Ask the recipient to state both time coordinates in its evaluation question, and identify which axis the inventory actually retains. If one is missing, describe the supported latest-view task or assign a bounded history-recovery question. A two-axis ledger documents version meaning; the correction’s substantive authority and the proposed disclosure still need their own evidence.