When It Happened vs. When You Knew: Why Data Has Two Clocks
Every fact carries two times: when it happened and when you could have known it. Mixing them up is a quiet reason many historical results look better than they should.
Synthetic dataExamples on this page use synthetic data created for illustration.
Not adviceThis is educational material. It is not investment, business or other professional advice.
On this page
Open any dataset of past events and you will find a date column. It looks like one fact. It is usually two, and the difference decides whether a historical result can be trusted.
One timestamp, two questions
Take a company’s quarterly revenue. The row says “Q4: 100”.
- When did it happen? The quarter ended on December 31. This is the event time.
- When could you have known? The company reported the figure on February 10. This is the knowledge time: the moment the number became available to someone looking from outside.
To describe the past, event time is what you want. To judge what someone could have done at a given moment, only knowledge time counts. A person standing on January 15 had no way to see a number that first appears on February 10. Database people call these two clocks valid time and transaction time; together they make data Bitemporal Data.
A worked example
The data below is synthetic. A team tests a simple rule: pay closer attention to a topic when its monthly count rises by more than 20%. The counts come from a source that publishes after each month ends and sometimes revises its figures.
| Month | First published | Published on | After revision | Revised on |
|---|---|---|---|---|
| Jan | 100 | Feb 5 | 100 | — |
| Feb | 130 | Mar 6 | 118 | Mar 20 |
| Mar | 140 | Apr 4 | 140 | — |
On March 6 the rule sees February at 130, a 30% rise over January, and fires. Two weeks later the figure is corrected to 118, an 18% rise, and the rule would not have fired.
A real-time user acted on the March 6 figure and was wrong. A test that reads today’s revised table never sees that mistake: it shows the rule staying quiet in February. The test result is cleaner than reality, because it quietly borrows a correction from March 20.
How the mistake gets in
Four routes account for most cases.
- Tables that keep only the latest version. Corrections overwrite the original, so the original is gone.
- Joining on the event date. A figure for a quarter is attached to the quarter-end date, not to the day it was published.
- Snapshots taken today. A table exported now, under old dates, describes today’s knowledge of the past.
- Delays hidden by rounding. A value stamped “end of day” that arrives the next morning looks available a night too early.
Each produces a table that looks like history and behaves like hindsight. The general error is called Look-ahead Bias.
Labels have two clocks too
The outcome you want to predict has a knowledge time as well. Suppose the label is “the customer left within 90 days”. For a prediction made on January 1, that label cannot be known until April 1.
The data below is synthetic. A team builds a training set on March 31 for a model that predicts churn within 90 days.
| Prediction made on | Label becomes known on | Usable in a March 31 training set? |
|---|---|---|
| Dec 1 | Mar 1 | Yes |
| Jan 1 | Apr 1 | No — the outcome is still unknown on Mar 31 |
| Feb 1 | May 1 | No |
The tempting shortcut is to fill the missing labels with whatever is known today, or to quietly treat “has not left yet” as “stayed”. Both mix the future into the training set. The honest rule is that a training example is only usable once its label is known, so the newest usable examples always lag behind the present by the length of the label window.
What to store
For every value, keep four things: what it says, when it was true, when you learned it, and a new row whenever it is corrected. Never overwrite.
With that in place, “as of” questions become simple. Take the latest row per fact whose knowledge time is not later than the moment you are simulating:
-- Q4 revenue as it was known on March 1
SELECT value
FROM revenue_history
WHERE period = 'Q4'
AND recorded_at <= '2024-03-01'
ORDER BY recorded_at DESC
LIMIT 1;Reading data this way is what Point-in-Time correctness means in practice.
When the source keeps no history
Sometimes the source only ever shows the latest version and offers no way to see earlier ones. Then the honest options are limited:
- Start recording now. Save a dated snapshot on a regular schedule. From that day on, you can answer “as of” questions correctly.
- Mark the earlier history. Anything before your first snapshot is “as currently reported”, not “as it was known”. Say so wherever the results are shown.
- Prefer sources that publish revisions. Some publishers release a revision history or vintage archive. If one exists, use it.
Treat results that depend on pre-snapshot history as less reliable, and say how much less.
A short checklist
- For every field used in a historical test, can you say when each value became known?
- Do corrections add rows, or overwrite?
- Are definitions versioned too, such as categories, membership lists and labels?
- Does the test fail loudly if it asks for something dated after the moment being simulated?
Why it matters
Results built on knowledge time are usually less impressive. That is the point. They describe what could really have been done, which is the only thing a historical test is for.
References
- Martin Fowler. Bitemporal History . martinfowler.com, 2021.
- Richard T. Snodgrass. Developing Time-Oriented Database Applications in SQL . Morgan Kaufmann, 1999. ISBN 978-1558604360.
- Marcos López de Prado. Advances in Financial Machine Learning . Wiley, 2018. ISBN 978-1119482086.