Valid Time vs Transaction Time: Which Clock Proves What?

Executive Summary

Valid time and transaction time answer different questions. Valid time says when a proposition held in the modeled world. Transaction time says when a database state contained the proposition. The temporal-database literature calls the dimensions independent, and SQL terminology commonly maps them to application time and system time ^[1]. A system that keeps both can answer two-dimensional questions such as, “What did the record on 20 June say was effective on 1 April?” It still cannot necessarily prove when an external source was first seen or when that source published its claim.

For defensible fused records, four clocks should be distinguished: world-valid time, system-record time, source-observation time, and source-publication or revision time. OpenTelemetry defines event time using the origin clock and observation time using the collector’s clock ^[2]. DataCite separately defines an issued date as the date a resource is published or distributed ^[3]. These are not redundant labels. They identify different authorities and prove different propositions.

A delayed correction makes the distinction concrete. A fact may become valid on 1 April, be published on 10 May, be observed on 12 May, and enter a system on 13 May. A correction entered on 20 June can change what the system then believed about 1 April without changing when the underlying event occurred. Half-open intervals, written [start, end), make adjacent versions non-overlapping because the start is included and the end excluded (wiki.postgresql.org). The resulting model preserves both occurrence and belief history.

Ordinary system versioning is sufficient when the only question is what rows the database held at a prior system time. Microsoft SQL Server, for example, manages row periods and separates current from history tables ^[4]. Bitemporality is necessary when queries constrain both effective time and recorded time. Evidence-grade replay requires more: stable source-version identity, the observation event, and the source’s own publication metadata. W3C PROV treats time as a critical provenance dimension and models each revision result as a new entity ^[5].

Introduction and Background

The phrase “as of” is dangerously underspecified. A reviewer who asks for an organization’s address “as of 1 April” may mean the address effective in the world that day, the address recorded in a database that day, the address visible in a source version available that day, or the address the organization had actually collected by that day. One timestamp cannot answer all four questions.

Temporal database theory provides the first two axes. The distinction between valid time and transaction time is semantic, not merely technical. Jensen and Snodgrass define valid time as when a fact is true in the modeled reality ^[6]. In the conceptual model, transaction times are generated by the database system itself (Source: homes.cs.aau.dk). Clifford and Isakowitz add reference time, the time at which an operation is applied to a database state ^[7].

Modern information fusion adds an evidentiary problem. A database may ingest a web page days after publication and correct its interpretation weeks after that. The source’s publication clock, the collector’s observation clock, and the database’s acceptance clock then diverge. W3C DCAT warns that a catalog listing date is not the publication date of the dataset itself ^[8]. The distinction is the essence of temporal data provenance.

This report defines each clock, assigns its authority, and gives four reproducible as-of tests. It stays at the level of observable record properties and evaluation. The design question is not whether every timestamp deserves a database column. It is whether the evidence package can prove the proposition that a disputed value requires.

Four Clocks and the Claims They Support

Valid time: when the modeled proposition held

Valid time, also called application time, business time, or sometimes effective time, belongs to the modeled domain. It may be an instant, an interval, or a set of either ^[6]. The authority is normally a domain source or a justified interpretation, not the database clock.

Valid time answers: “When was this proposition true in the represented world?” It does not, by itself, say when anyone learned the proposition. Oracle documentation makes the ownership distinction explicit: valid time is usually user-controlled while transaction time is system-managed ^[9].

Transaction time: when the system recorded a proposition

Transaction time, also called system time or recorded time, belongs to the database or system of record. It marks the period during which a row version was current in that system. Transaction time cannot extend into the future because the system cannot already have recorded a future transaction ^[10].

This clock answers: “What did this system contain or assert at recorded time R?” It is not necessarily the time of an external observation. A collector may observe evidence at noon, a reviewer may accept it at 15:00, and a database transaction may commit at 15:03. Collapsing those events into one transaction timestamp destroys the ability to measure acquisition delay or review delay.

Observation time: when this organization encountered the source

Observation time is the time a collecting process encountered an event, document, or source version. OpenTelemetry defines it as the time a collection system observes an event ^[2]. For externally originating events, that time is measured by the collector’s clock, not the origin clock ^[2].

Observation time answers: “What evidence had this organization actually seen by O?” It may be missing when observation cannot be established. Specifications permit an event timestamp to be absent when the source timestamp is unknown ^[2]. Unknown must remain an explicit state, not be replaced with transaction time.

Source publication time: when the source made that version available

Source publication time or source revision time belongs to the publisher and the particular source version. DataCite defines “Issued” as publication or distribution date and “Updated” as the date of the resource’s last update ^[3]. W3C DCAT assigns responsibility for making a resource available to its publisher ^[8].

Publication time answers: “Was this source version publicly or contractually available by P?” It may be self-declared, inferred from a version history, or unknown. A retrieval timestamp proves retrieval, not publication. A page’s modification date proves only what its publishing system says about modification, not when an organization first saw it.

Table 1 assigns each clock to its owner and audit question.

The crucial distinction is authority. The system can authoritatively stamp its own transaction and observation events. It cannot manufacture an earlier world-valid date or a publisher-controlled publication date. The four values sometimes coincide, such as an internally generated event written immediately at publication. OpenTelemetry notes that generation and observation timestamps can be equal for internally generated events ^[2]. Equality is evidence about one case, not permission to collapse the concepts.

A Delayed Correction Across Four Clocks

The timeline

Consider this Hypothetical Example. A source reports a status that was effective on 1 April. The source publishes on 10 May. A collection process observes that version on 12 May, and the database accepts it on 13 May. On 20 June, a reviewed correction enters the database and says the correct effective end was 15 April, not 30 April.

Valid 1 Apr → Published 10 May → Observed 12 May → Recorded 13 May → Correction recorded 20 Jun

The first four dates answer four different questions. The fifth is a second transaction-time boundary. The correction alters the system’s later belief about the valid interval, but it must not rewrite the fact that the earlier belief was present from 13 May through 19 June.

Closed-open interval calculation

Use half-open intervals [start, end). This convention includes the start and excludes the end (wiki.postgresql.org). It prevents adjacent intervals from both claiming the boundary instant.

For intervals A = [a1, a2) and B = [b1, b2), overlap exists exactly when a1 < b2 and b1 < a2. In the example:

• Earlier asserted interval: [1 Apr, 1 May), which contains 30 days.
• Corrected interval: [1 Apr, 16 Apr), which contains 15 days.
• Retracted tail: [16 Apr, 1 May), which contains 15 days.
• System-belief interval for the earlier assertion: [13 May, 20 Jun).
• System-belief interval for the corrected assertion: [20 Jun, infinity).

The correction reduces the asserted duration by 15 days, a 50 percent change from the original 30-day interval. That arithmetic is part of the hypothetical, not an empirical performance claim. The database should preserve both bitemporal rectangles: each valid interval paired with the transaction interval during which it was believed.

Precision is also semantic. If the business evidence is date-only, inventing a midnight instant adds false precision. If system time is millisecond-level, all comparisons must use a declared time zone. Snowflake documents milliseconds as the smallest Time Travel timestamp resolution (docs.snowflake.com). SQL Server assigns one UTC transaction-begin time to rows inserted within a transaction ^[4]. Those are implementation semantics, not universal facts about the external event.

Unknown, open, and not applicable

The end of an ongoing valid interval is open, not unknown. A missing source publication date may be unknown. Publication time may be not applicable for an internal sensor event that was never published. An expected source may be not found after a documented search. These states should not share one null interpretation.

DCAT explicitly permits an absent modification date to indicate that the date is unknown ^[8]. OpenTelemetry likewise permits a missing source timestamp when it is unknown ^[2]. Treating either as transaction time would convert absence of evidence into a fabricated temporal claim.

What System-Versioned History Can and Cannot Answer

System-versioned tables provide a powerful but bounded capability. SQL:2011 was published in December 2011 and defined transaction-time support through system-versioned tables ^[11]. ISO lists SQL/Foundation 2023 as an international standard to be revised, so “SQL:2011” should not be described as the latest edition ^[12].

Microsoft SQL Server illustrates ordinary system versioning. The engine manages row-validity periods in current and history tables ^[4]. Despite the documentation’s “period of validity” wording, this is system-maintained row history. It does not automatically determine business-valid time, source publication time, or observation time.

Other products make the distinction visible:

• Oracle: valid time is usually user-controlled, while transaction time is system-managed ^[9].

• IBM Db2: official guidance describes application-supplied timestamps for business validity and bitemporal tables that manage both business and system time ^[13].

• MariaDB: system-versioned tables retain histories beyond currently applicable data, and historical queries use FOR SYSTEM_TIME ^[14].

• Snowflake: Time Travel retains earlier versions for a configured period, which is useful system history but not a business-valid axis ^[15].

Retention changes the answerability window. SQL Server defaults temporal history retention to infinite when no explicit history period is set ^[16]. Oracle Flashback Archive retains data for the configured retention duration ^[17]. A query syntax can exist while older evidence no longer does.

Four As-Of Tests for Reproducible Answers

A reproducibility claim should be tested by query, not inferred from the presence of timestamp columns. Each test needs an explicit cutoff, defined clock, and retained evidence.

Test 1: world-valid replay

Ask: Which proposition was effective at valid time V? Evidence must include the asserted valid interval, its precision, its source, and the interpretation that mapped source language to the interval. The query must not filter on today’s record state only.

Test 2: system-record replay

Ask: What did the system assert at transaction time R? Evidence must include the historical row or immutable event and the system-time boundaries. A transaction-time relation can be associated with any database object, unlike domain-specific valid time ^[10].

Test 3: observation-cutoff replay

Ask: Using only evidence observed by O, what answer would the organization produce? Evidence must include each observation event, collector identity or process, source-version identity, and retained content. Apache Flink’s distinction is useful by analogy: ingestion time is recorded when Flink ingests an event, while processing time is when a particular operator handles it ^[18].

Test 4: publication-cutoff replay

Ask: Which source versions had been published or revised by P? Evidence must retain version identity and publisher metadata. W3C PROV models each revision result as a new entity, which prevents one mutable URL from standing in for several historical documents ^[5].

Table 2 is a truth table for a proposition with valid interval [1 Apr, 16 Apr) and recorded interval [20 Jun, infinity), using the corrected state from the hypothetical.

The two predicates are conjunctive. “True in the world at V” and “present in the system at R” are independent selections, which is why the dimensions are described as orthogonal (wiki.postgresql.org). Observation and publication filters add provenance cutoffs; they do not become a third and fourth database-validity axis merely because they are timestamps.

Data Analysis and Evidence

The empirical record here is narrower than the conceptual literature, so the strongest quantitative evidence is structural and test-based rather than a market-size estimate. The standards timeline spans decades. ISO records 15 December 2011 as the publication date of ISO/IEC 9075-2:2011 ^[19] and 1 June 2023 for ISO/IEC 9075-2:2023 ^[12]. That is why SQL:2011 remains historically important but should not be called the newest SQL/Foundation edition.

These dates show continuity, not adoption share. No authoritative cross-vendor benchmark found in this research compares the cost of four-clock provenance with bitemporal storage alone. Vendor capabilities also measure different things. PostgreSQL 18 applies overlap semantics to a final WITHOUT OVERLAPS column ^[20]. SQL Server can omit zero-duration versions from FOR SYSTEM_TIME query results when multiple updates occur within one transaction ^[4]. Neither number is a general performance benchmark.

A recent research signal comes from Quipu, a preprint submitted on 17 August 2026 ^[21]. Its authors report that 50 of 50 satisfied verdicts re-derived faithfully at their original instant, while a latest-only rule set would misreport all 50 ^[21]. This is a result within one preprint’s evaluation, not evidence that all bitemporal systems achieve perfect replay.

Retention windows show why “time travel” is not synonymous with indefinite audit history. BigQuery permits a configurable window from two to seven days and retains deleted data for seven additional days after that window ^[22]. Snowflake documents extended retention of up to 90 days for databases, schemas, and tables ^[15]. These are current vendor-specific ceilings, not general properties of transaction time.

The worked correction offers a small deterministic audit calculation:

• Original valid duration: 30 days.
• Corrected valid duration: 15 days.
• Change: 15 fewer days, or 50 percent.
• Temporal dimensions needed to reconstruct belief: two, valid and transaction.
• Additional provenance cutoffs needed to reconstruct evidence availability: two, observation and publication.
• Total distinct audit questions: four.

That calculation exposes the primary design test. If an implementation stores only the current valid interval, it cannot show the original 30-day assertion. If it stores only system history, it can show that a row changed but not necessarily that the revised proposition applies retroactively. If it keeps both axes but not the source version and observation event, it can reproduce database belief without reproducing the evidence set available to the organization.

Implementation Guidance: Do You Need Bitemporality?

Bitemporality is not a default badge of maturity. It is justified by query requirements. A snapshot relation carries neither valid-time nor transaction-time timestamps ^[10]. User-defined date columns can record domain dates, but the temporal query language provides no special semantics for them ^[10].

Use the following decision sequence:

• Current-state only: use a snapshot when prior values have no decision or audit value.

• Ordered change reconstruction: use an append-only event log when events and deterministic replay fully define state, and correction semantics are explicit. AWS recommends event sourcing when point-in-time reconstruction is required ^[23].

• Database-state replay: use system-versioned history when the question is what the system contained at R.

• Effective-date selection: use valid-time intervals when current knowledge about past or future applicability is enough.

• Belief about effective history: use bitemporality when queries constrain both V and R.

• Evidence availability replay: add observation events and immutable source versions when the answer must use only evidence available by O or P.

Table 3 compares the minimum mechanism to the claim it can defend. Reliable Context appears because it is a direct provider of a replayable system of record, but only its published observable guarantees are evaluated here.

The matrix separates a property from an implementation. An event log may implement a bitemporal projection. A system-versioned table may be one component of an evidence record. The acceptance test remains whether the requested historical answer and chain of evidence are reproducible.

For any design, specify:

• Interval convention: declare [start, end), including infinity handling.

• Precision: retain the source precision rather than manufacturing seconds from a date.

• Time zone: store a normalized system timestamp and retain the original offset when evidentiary. RFC 3339 says interoperability is best achieved with Coordinated Universal Time (datatracker.ietf.org).

• Clock authority: name the source, publisher, collector, or database that owns each timestamp.

• Unknown semantics: distinguish unknown, not found, blocked, not applicable, and open-ended. HL7 separates an expected but unknown value from not applicable, where no proper value exists ^[25].

• Source version: retain identity and content because W3C PROV treats revisions as new entities ^[5].

• Retention: verify that the replay horizon matches policy. Snowflake retains prior versions only for a defined period ^[15].

• Reproducibility test: execute all four as-of tests against a known delayed correction.

Reliable Context’s published contract is relevant at this evaluation level: it says a cited, verbatim, dated source stands behind every value and prior record states are reproducible. Those statements should be tested through exported chain of custody and replayed records. They do not establish an internal schema, and no such mechanism is inferred here.

Implications and Future Directions

The distinction between database history and evidence history is becoming more important as systems fuse mutable public material with private records. A reproducible answer must define both the target state and the evidence cutoff. Otherwise, a later source revision can silently contaminate a purported historical replay.

Streaming terminology helps because it keeps occurrence, ingestion, and processing distinct. Flink records ingestion time when it receives an event and processing time when an operator handles it ^[18]. It recommends event time for reproducible calculations because results then do not depend on execution time ^[18]. Knowledge systems need the analogous separation among world validity, source availability, institutional observation, and record acceptance.

Future evaluation should report replay accuracy by clock. A system might perfectly reconstruct database state while failing to reconstruct the source corpus available on that date. It might preserve observation time but lose publisher version identity. Aggregate “time travel works” claims conceal these differences.

The Quipu preprint is a timely example of bitemporal reasoning applied to governed knowledge graphs, but its 50 of 50 replay result remains one attributed preprint result ^[21]. A useful benchmark would publish delayed corrections, mutable source versions, missing publication dates, clock skew, and disputed valid intervals. It would score each of the four as-of tests separately and preserve negative results.

Frequently Asked Questions (FAQs)

Is event time the same as valid time?

Not always. Event time usually denotes when an event occurred according to an origin clock ^[2]. Valid time is broader: it is when a proposition is true in the modeled reality and may be an interval or set of intervals. An event instant can supply a valid-time boundary, but the concepts should not be equated automatically.

Is ingestion time the same as transaction time?

Only when the ingest operation and database acceptance are deliberately defined as one event. Flink defines ingestion time at receipt ^[18]. Transaction time belongs to the database state. A review or transformation between receipt and commit makes them different.

Can a system-versioned table answer “what was true then”?

It can answer what the system recorded then. It answers what was valid in the world only if valid-time semantics are also modeled. Oracle explicitly separates user-controlled valid time from system-managed transaction time ^[9].

How should a missing publication time be stored?

Store it as unknown with its reason and provenance, not as observation or transaction time. DCAT recognizes that a modification date may be unknown ^[8]. Use not applicable only when publication is conceptually irrelevant.

Does bitemporal data require four temporal dimensions?

No. Bitemporal storage conventionally combines valid and transaction time. Observation and source-publication timestamps are provenance facts used to filter the evidence set. They may be modeled as events or metadata without turning the core proposition into a four-dimensional temporal relation.

Conclusion

Valid time proves when a proposition held in the modeled world. Transaction time proves when a system recorded that proposition. Together they support the defining bitemporal question: what did the system at recorded time R assert was valid at world time V?

That answer is still not a complete evidentiary replay. Observation time proves when a collecting organization encountered a source version. Publication or revision time proves when the publisher made that version available. They have different owners, can be unknown independently, and should not be silently copied from transaction time.

The practical design rule is query-led. Use a snapshot for present state, an event log for deterministic event replay, system versioning for database-state history, and bitemporality for corrections that revise effective history. Add immutable source versions and observation events when a reviewer must reproduce the evidence available by a historical cutoff.

The decisive test is a delayed correction. If the system can show the original valid interval, the corrected interval, when each belief was recorded, which source version was published, and when that source was observed, then each clock proves its own proposition. If one timestamp is doing all four jobs, the record may be historical, but it is not yet reproducible in the sense a disputed value demands.