CSE Roadmap

Specialist tier · about 3 hours · 5 parts

Part 2 of 5 · 1 min read

Valid time versus transaction time

Two different, independent notions of "when," often confused for each other.

Think of a contract that takes effect on a certain date, versus the separate, different date someone actually typed it into the filing system.

In plain words: valid time is when a fact was true in the real world. Transaction time is when the database actually recorded that fact. The two can differ: a price change might be recorded today but take effect, in valid time, starting next Monday.

A worked example. A book's price increase is entered into the system on March 1st, but is set to take effect on March 15th. March 1st is the transaction time; March 15th is the valid time.

The key lines.

CREATE TABLE book_price_history (
    book_id       INTEGER NOT NULL REFERENCES books(book_id),
    price         NUMERIC(6,2) NOT NULL,
    valid_from    DATE NOT NULL,
    recorded_on   TIMESTAMP NOT NULL DEFAULT NOW()
);

Valid time, transaction time

Valid time is when a fact was true in the real world. Transaction time is when the database recorded that fact. The two can differ, especially for changes scheduled ahead of when they take effect.

Example: valid_from captures valid time; recorded_on captures transaction time, and the two columns can legitimately hold different dates for the same row.