Yesterday’s answer can change.

Picture a single sheet of paper: a cancellation form for a subscription. In the customer’s handwriting, the box marked “effective date” says January 31, 2026. Across the top, in ink from a rubber stamp, the office has added a second date: received February 3. Below that, someone has initialed it and written “entered 10:42.” (The form, the company and every number below are invented for this essay. They describe no real business.)

Now consider two reports about the same night. On February 1, the finance team ran the month-end count and found 4,812 customers at the close of January. On March 1, someone runs the same count, with the same definition, for the same night, and gets 4,811. The definition never moved: a customer is an account with an active subscription at the close of the day in question, and a subscription cancelled effective January 31 is not active at the close of January 31. Nobody made an arithmetic mistake. The form arrived late, and the second count knows about it.

Which number is right? Both are, because they answer different questions. One asks what was true on January 31, as best we know now. The other asks what the system said about January 31 on the morning someone used it to make a decision. My argument is that a trustworthy record keeps two dates for each fact, when the fact was true and when the system recorded it, wherever both questions matter. That does not mean every table needs two clocks, that every old row must be kept forever, or that the system’s history proves what really happened. The claim is narrower: the current best account of the past and the account available to a past decision are different queries, and a record that can answer only one of them will eventually be asked the other.

Two questions about one night

Follow the two numbers through an ordinary month. On February 1 the count of 4,812 goes into a board pack, a churn rate and perhaps a commission calculation. Decisions are made from it. A few days later the late form is entered, and the account’s status changes. By March, anyone who asks how many customers the company had at the end of January should get 4,811, because that is the better answer to the question they are asking.

Then someone asks a different question. Why did the board see 4,812? Was the finance team careless? Should the commission be clawed back? Did the forecast that used the February number fail because the model was wrong, or because its input was later revised? None of these can be answered with today’s best account of January 31. Each needs the answer the system gave on February 1, and an explanation of why it later changed.

The two questions differ only in which clock is held still. The first fixes the night being described and lets the system’s knowledge be as current as possible. The second fixes the moment of knowing as well, and asks what the record said then. Put that way, it is obvious that a single date per fact cannot serve both. Yet a great many systems keep only one.

What an overwrite destroys

The most common way to handle the late form is to update the account: change its status from active to cancelled, perhaps stamp the row with the time of the edit, and move on. The current answer is now correct. But the old one is gone. Rerun the February 1 report and it says 4,811. The record no longer contains any trace that the system once said 4,812, so the board pack now disagrees with the database, and the person who prepared it looks as if they made an error they did not make.

The opposite habit fails the other way. Some teams close a month and refuse to touch it, so that every report from February 1 can be reproduced forever. That protects the record of the decision but leaves the history wrong: the company will report, indefinitely, that account 20417 was a customer on a night when it was not. An overwrite gives you correction without accountability. A frozen month gives you accountability without correction. Each sacrifices one question to protect the other.

The way out is to stop treating a correction as an edit and start treating it as a new fact with its own date of arrival. On February 3 the system learned something. It can write that down, leaving the earlier belief in place and marking when it was superseded. Then both questions have an answer, and the answers can disagree honestly.

Two clocks, named

Database researchers worked this out decades ago and gave the two clocks names. In a 1985 paper that set out a taxonomy of time in databases, Richard Snodgrass and Ilsoo Ahn distinguished transaction time, the time a piece of information was stored in the database, from valid time, the time when the thing it describes was true in the world being modeled.¹ They noted that earlier work had used related ideas under other names; their contribution was to separate them cleanly.

In a 1999 survey, Christian Jensen and Snodgrass sharpened the distinction. A fact’s valid time may lie in the past, the present or the future, since a record can say that a price takes effect next month. Its transaction time runs from the moment the fact is inserted until it is logically deleted, and because it is set by the act of recording, the database itself can supply it. Transaction time only moves forward, bounded by the day the database was created and the present moment.² A record that keeps both is called bitemporal, and Jensen and Snodgrass state the meaning of a pair of timestamps precisely: the state of the database at the transaction time records that the fact is valid at the valid time.² Translated to the form: as of February 1, the database recorded that account 20417 was active on January 31. As of February 3, it recorded that it was not.

The idea has since reached the SQL standard. SQL:2011 added what it calls application-time period tables, for the periods during which data is believed valid in the real world, and system-versioned tables, which keep an accurate history of changes to the data. In a system-versioned table the system, not the user, maintains the start and end of each row’s period, and users cannot change historical rows. A query can ask for the table’s contents FOR SYSTEM_TIME AS OF a past moment. A table can have both kinds of period at once, which its authors call bitemporal.³ Snodgrass’s 1999 book works through how to build such tables in ordinary SQL, at length, which is itself a hint about the cost.⁴

The figure below is that idea on paper: a small ledger of the facts about one account, in which nothing is crossed out.

Each question picks rows with both clocks. Asking what the system said on February 1 keeps only the rows whose recorded period includes February 1, which is row 1 alone, and then reads off what that row says about January 31: active. Asking what is now believed about January 31 keeps the rows recorded through the present, rows 2 and 3, and finds that January 31 falls in row 3’s true period: cancelled. The same ledger gives 4,812 and 4,811, and it can explain the difference in one sentence: on February 3 the system learned of a cancellation effective January 31.

That sentence is what an overwrite throws away. With it, the board pack is not an error but an accurate report of what the company knew, and the March figure is not a contradiction but a correction. Both people who produced a number can be right, and anyone can see why.

Economists learned this the hard way

The clearest evidence that the second question matters comes from macroeconomics, where official statistics are revised for years after first publication. In the late 1990s, Dean Croushore and Tom Stark at the Federal Reserve Bank of Philadelphia built a data set of “vintages”: snapshots of the major economic series as they stood on a given date, so that a researcher could see the data available to a forecaster or policymaker at that moment rather than only the latest revised numbers.^5,6 Each vintage is, in effect, the answer the system gave on one morning.

Athanasios Orphanides, then at the Federal Reserve Board, showed why that matters for judging decisions. He compared the interest rates that a popular policy rule would have recommended using the data policymakers actually had with the rates it recommends using the revised data published later. The two sets of recommendations differed considerably. In the working-paper version of the study, the gap between them had a standard deviation of 58 basis points and at its largest approached two percentage points.^7,8 Studies that evaluate past policy with later data are grading decisions against information nobody had. “Reliance on ex post revised data can also prove misleading,” he wrote.⁸

The lesson cuts both ways. To learn what the economy actually did, you want the latest and best estimate. To judge whether a decision was reasonable, you want the data as it stood when the decision was made. A company reviewing a churn forecast, a credit decision or a sales commission faces exactly the same pair of needs, usually without a Philadelphia Fed to keep the vintages for it.

A faithful record of a mistake

There is a limit to what the second clock can promise, and it is easy to overstate. A bitemporal record proves what the system said and when it said it. It does not prove that what it said was true. Suppose the clerk had keyed the effective date as January 13 instead of January 31. The ledger would preserve that error faithfully, with a precise transaction time, and every query about what the system believed would return it. Correcting it would mean a new row, recorded on the day the error was found, which is exactly as it should be. History of the record and truth about the world are different things, and only the first is guaranteed.

The recorded time also says when the database learned something, not when a person did. If the customer had told an account manager on a phone call on January 30, the company knew before February 3 in every sense that matters to a customer, and the stamp on the form would hide it. A system timestamp is evidence about the system. Questions about what a human knew need their own evidence: the email, the call log, the mailroom stamp.

Objections, considered

“This is expensive and complicated.” It is. Jensen and Snodgrass note that temporal data can be very difficult to manage with conventional data models and query languages; in one of their examples, asking for a simple history takes 25 lines of SQL. Temporal tables grow large, need careful indexing, and make queries harder to optimize.² Built-in support in the standard reduces the effort but does not remove the extra storage, the extra design work, or the chance that someone writes the wrong query and mixes the two clocks. The cost is worth paying for a handful of records, not for everything.

“Most records need only one clock.” Agreed. Jensen and Snodgrass themselves say either kind of time may be left out when the applications do not need it.² A product description that is simply replaced when it changes needs neither. An audit log of who changed what needs only the recorded time. A price schedule with future effective dates may need only the true time. The case for both clocks is strongest where facts arrive late or get corrected and where past decisions will be reviewed: revenue, customers, eligibility, credit, compensation.

“Keeping every version conflicts with deletion obligations.” It can, and the obligations win. Under the European Union’s General Data Protection Regulation, personal data may be kept in identifiable form no longer than its purposes require, and people have a right to have it erased in many circumstances.⁹ A history table is still a table of personal data. Designing for two clocks means designing for retention and deletion at the same time: deciding which history must be kept, for how long, who may query it, and how an erasure is carried out across versions. This is an argument for knowing what you keep, not for keeping everything.

“Just save the report.” Often that is enough, and it is what the Philadelphia vintages are: periodic snapshots.⁶ A filed copy of the February 1 board pack proves what it said. But a snapshot answers only the questions someone thought to ask that morning. If, in April, an auditor wants to know which accounts the February count included, or how the churn model would have scored on the data available then, a saved PDF cannot answer. The fuller record matters exactly where the new questions are hard to predict.

Correcting the past and remembering what you knew about it are two different jobs, and a good record does both.

Back to the form

Before choosing a database feature, pick one report that matters and answer four questions about it.

• Which report will be both corrected and reviewed? Look for numbers that feed decisions and are later revised: customers, revenue, eligibility, commissions.
• What is the question about the world? Write it down with its definition held fixed, such as how many customers were active at the close of January 31.
• What is the question about the decision? Write that down too, such as what the system said on the morning the board pack was produced.
• What must be kept, and for how long? Decide which history is needed, who may see it, and how it will be deleted when the time comes.

Look again at the form. Nothing on it is wrong. The customer’s handwriting says when the fact was true, and the office’s stamp says when the system recorded it. The paper keeps both dates without any effort, side by side on one sheet. A record built to carry the same two dates can say, on any later morning, what happened and what it knew, and the answer to yesterday’s question can change without anyone pretending it never said something else.