learning-it by example

#265 Data modelling 6.6 min

Valid Time, Transaction Time and Bitemporal Tables

One price correction stored three ways; only the bitemporal table answers all 4 questions as asked.

1. In a temporal database, what is the difference between valid time and transaction time?
2. One made-up price was stored in a valid-time table, a transaction-time table and a bitemporal table, then four questions were asked of each. Which table answered all four questions as asked?
3. After three made-up price events were recorded in SQLite, how many rows did the valid-time, transaction-time and bitemporal tables hold?
4. In the SQLite price example, how does the bitemporal table handle a correction, so that nothing is lost?
5. The example stores every period as start included and end not included, with ISO text dates. Why is this a good choice?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. Predict the four questions

    Copy the code folder first. Read the QUESTIONS list in run.py and the three events in prices.py. For each of the four questions, predict what the valid only, txn only and both columns will say. Then run python run.py ask and mark where each simple table gives the wrong answer.

    A hint

    The bitemporal column is the only one that checks both the real-world day and the day it was known.

  2. Add two more questions

    Add the questions ("2026-02-15", "2026-03-05") and ("2026-02-15", "2026-03-04") to the end of QUESTIONS in run.py. Predict the bitemporal answers, then run python run.py ask. When I ran it, the bitemporal column said 11 for as known on 2026-03-05 and 12 for 2026-03-04, while the txn only column said 12 for both.

    A hint

    The correction is entered on 2026-03-05, and a row is believed from that day on.

  3. Make the correction later

    In prices.py, change the day of the third event from 2026-03-05 to 2026-03-20. Predict which of the four questions change, then run python run.py ask and python run.py rows. When I ran it, of the original four questions only the first changed in the both column, from 11 to 12, and the last bitemporal rows were recorded from 2026-03-20.

    A hint

    On 2026-03-10 the correction is not yet known.

These tasks are for you to try on your own machine. Nothing is checked or saved.

What this does not show

One made-up price and three events in SQLite, whole days only, no overlap constraint, and SQL:2011 known only from a summary.

Up next in Data modelling

More in Data modelling

All Data modelling videos

Related videos