Bitemporal Data Modeling: Valid Time vs. Transaction Time

1. Why a Record Often Needs Two Independent Time Axes

A single timestamp column such as updated_at answers only one question: when was this row last physically written. It does not answer the far more important business question of when a value was actually supposed to become valid. On a later correction, say because a price was entered incorrectly on January 1st and the mistake was only noticed on March 15th, both questions collapse into a single, unusable answer inside one timestamp column.

Bitemporal modeling consistently splits these two questions into two independent intervals: valid time describes the business period a value applies to, while transaction time describes when that information was considered known inside the system. Together, both axes allow questions such as: what did we know on February 1st about the price that applied on January 1st.

2. Valid Time: When a Fact Was Actually True

Valid time is usually set by business logic or by a user, not automatically by the database. It describes a period from the real world: an employee salary applies from the first of a month, a price change applies from the day a contract was signed, a discount applies for a defined promotional window. That period can also lie in the future, for example when a raise is recorded today but only takes effect on the first of next month.

Importantly, valid time can be corrected retroactively without changing transaction time at all. When an error in the original valid time is discovered, a new row with a corrected valid time is created, carrying its own new transaction time, while the old, incorrect row remains traceable in the history.

3. Transaction Time: When Information Became Known to the System

Transaction time, on the other hand, is almost always set automatically by the database or application and is fundamentally immutable afterwards, because it describes a fact about the system itself: at what point did this database know about this value. That very property is what makes transaction time a reliable foundation for audits, since it cannot be manipulated retroactively by definition without that becoming visible in the data structure.

In practice, transaction time usually consists of two timestamps, analogous to system-versioning: a start point from which a given row version was the currently known state, and an end point from which it was superseded by a newer version. As long as a row is the currently known state, its transaction end typically stays set to a maximum value such as 9999-12-31.

4. Combining Both Axes: A Bitemporal Table With Four Timestamps

In the table itself, this results in four timestamp columns instead of one: valid_from and valid_to for the business valid time, plus recorded_from and recorded_to for the technical transaction time. Every row thereby describes exactly one combination of a validity period and a recording period, and a correction never produces an UPDATE on an existing row, only ever a new row with a new transaction time.

This structure looks unusual at first, because the same business validity period can end up spread across several rows with different transaction times once a retroactive correction has been made. That is precisely the point, though: the table holds not just the currently correct value but the entire history of what was considered correct at any given moment.

CREATE TABLE price_bitemporal (
    price_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id    INT NOT NULL,
    price_cents   INT NOT NULL,
    valid_from    DATE NOT NULL,
    valid_to      DATE NOT NULL DEFAULT '9999-12-31',
    recorded_from TIMESTAMP NOT NULL DEFAULT now(),
    recorded_to   TIMESTAMP NOT NULL DEFAULT '9999-12-31 00:00:00'
);

5. Practical Example: Keeping a Retroactive Price Correction Traceable

On January 1st, a product price of 4000 cents is recorded, valid from that same day. On March 15th it turns out the price should have actually been 4500 cents from January 1st onward, a pure data entry mistake at the time. Instead of simply overwriting the old row, its recorded_to is set to March 15th, closing it off, and a new row with valid_from set to January 1st but recorded_from set to March 15th is inserted.

This makes it possible to later answer exactly what a report generated on February 1st would have shown, namely still 4000 cents, even though the correct business price for that day is now known to have been 4500 cents. That distinction is often essential for financial reports that must remain reproducible as of a specific reporting date.

-- Close off the old row (end its transaction time)
UPDATE price_bitemporal
SET recorded_to = '2026-03-15 09:00:00'
WHERE product_id = 100 AND recorded_to = '9999-12-31 00:00:00';

-- Record the corrected value with retroactive validity
INSERT INTO price_bitemporal
    (product_id, price_cents, valid_from, recorded_from)
VALUES
    (100, 4500, '2026-01-01', '2026-03-15 09:00:00');

6. Querying With Combined Time Points: As Of Valid Time and As Of Transaction Time

A bitemporal query typically takes two points in time instead of one: one for valid time and one for transaction time. That allows four fundamentally different questions to be answered: the currently known, currently valid value; the currently known, historically valid value; the once known, currently valid value; and the once known, once valid value, as in the reproduction example above.

For reproducible reporting it is usually enough to set both points to the same historical date: what was actually true on that day, based on the state of knowledge as of that same day. That is meaningfully different from a plain valid-time query, which would apply today's state of knowledge to a past period and thereby let retroactive corrections silently leak in.

-- Bitemporal query: valid on 2026-01-15, as known on 2026-02-01
SELECT price_cents
FROM price_bitemporal
WHERE product_id = 100
  AND valid_from <= '2026-01-15' AND valid_to > '2026-01-15'
  AND recorded_from <= '2026-02-01' AND recorded_to > '2026-02-01';

7. Modeling Patterns: Combining Separate Axes Instead of Reinventing Them

Instead of building both axes entirely by hand, transaction time can often be mapped directly through SQL:2011 system-versioning, while valid time is added as a second, application-managed period through PERIOD FOR APPLICATION_TIME, provided the database system supports both concepts. IBM DB2 already combines both mechanisms natively into true bitemporal tables, where the database manages transaction time automatically and the application only has to set valid time.

In systems without this native support, such as PostgreSQL or MySQL, the only option is the manual route through explicit timestamp columns and disciplined insert-instead-of-update rules, ideally encapsulated in a dedicated data access layer so that no caller accidentally overwrites a bitemporal row directly.

8. Assessing the Modeling Effort Realistically

Bitemporal models are noticeably more effort than simple timestamps: every query needs four time conditions instead of two, every write has to be thought of as an insert instead of an update, and reports have to explicitly state which combination of valid time and transaction time they actually mean. Teams without prior experience with this pattern tend to see a higher error rate in the first few weeks.

The effort pays off above all where both questions genuinely need to be answered separately on a regular basis, such as in finance, contract management, or regulated industries with audit obligations. For ordinary business applications, where only the currently valid version matters and corrections are rare and uncritical, a fully bitemporal model is usually overkill.

9. Common Mistakes When Introducing Bitemporal Models

The most common mistake is confusing the two axes: developers set valid_from to the current moment out of habit, even though business validity should actually lie in the past or future, quietly defeating the entire purpose of the separation. A second common mistake is missing constraints against overlapping valid time periods within the same transaction period, which can lead to contradictory values being valid at the same time.

A third mistake is accidentally maintaining transaction time through an UPDATE instead of an insert chain, silently overwriting what was supposed to be an immutable history. Anyone introducing bitemporal tables should enforce these rules in a dedicated access layer instead of relying on the discipline of every individual caller.

10. Summary

11. FAQ: Bitemporal Modeling at a Glance