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.