AI Glossary / Data engineering and architecture / Intermediate

Bitemporal modeling

Definition

Bitemporal modeling represents two time dimensions for a fact: when it is effective in the modeled world and when it is recorded as known by the system. It supports both corrected historical views and reconstruction of what the system previously believed.

What it means

An address change may take effect on Monday but arrive in a company's database on Thursday. Asking “what was the address on Tuesday?” can therefore mean two different things: the address now understood to have been effective then, or the address the system had recorded when Tuesday's work was performed.

The effective dimension is often called valid time or business time. The recording dimension is often called transaction time or system time. Terminology varies, so the meanings should be defined explicitly.

Bitemporal information preserves the difference between a change in the world and a change in recorded knowledge. It does not make every recorded assertion objectively true. A correction may itself later need correction, and the model can retain that progression.

How it works

A common design associates a fact with an effective interval and a recorded interval. The effective interval says when the fact applies; the recorded interval says when that version represented the system's knowledge. Corrections close or supersede recorded versions while retaining enough history to reconstruct earlier views.

A query supplies both perspectives: an effective date and an “as known at” date. If only one is supplied, the application needs an explicit default, such as using current recorded knowledge.

Database support varies. IBM Db2's bitemporal tables combine application-period and system-period information. Its business-time periods use an inclusive start and exclusive end, but interval conventions should be documented rather than assumed across systems. Querying dates at boundaries, handling future-effective changes and preventing unintended overlaps are important parts of the design.

Three practical examples

• A customer service system explains why a delivery used an old address before a delayed change was recorded.
• An equipment maintenance system preserves both a corrected installation date and the earlier date used in a previous service decision.
• An AI evaluation pipeline reconstructs which facts were available at a historical prediction time, reducing the risk of evaluating past decisions with information recorded only later.

Think of it this way

Think of a diary that preserves both the date an event happened and the date you learned about it. A later correction can change your account of the event without changing the fact that your earlier entry said something different.

The analogy helps separate the two clocks. A database needs more precise rules for intervals, identifiers and updates than an ordinary diary. It also cannot infer the correct event date merely from the date someone entered a record.

Worked example

Answering two historical questions

Suppose an invented service records on 1 January that customer C7's address is Oak Street, effective from 1 January onward. On 10 March it receives a correction: the customer moved to Pine Street effective 1 March.

Ask for the address effective on 5 March as known on 6 March. The answer is Oak Street, because the correction had not yet been recorded. Ask for the address effective on 5 March as known on 11 March. The answer is Pine Street.

Now a parcel was dispatched on 6 March using the then-recorded address. The corrected view does not mean the dispatch system secretly knew Pine Street that day. Bitemporal queries explain the distinction. They do not automatically redirect the parcel, decide responsibility or calculate compensation. Those are separate workflow decisions. Using half-open intervals, the corrected effective history changes at the start of 1 March without requiring two different current addresses to overlap at that boundary.

Illustrative case study

A fictional facilities company discovers that a machine's commissioning date was entered a month late. Overwriting the date would improve the current record but make a past maintenance report difficult to explain.

The team preserves the earlier recorded version and adds the corrected effective history. Its report interface offers two clearly labeled views: the history understood now and the information available when an earlier report was prepared. Reviewers also retain the report's relevant software and rule versions, because data history alone does not reconstruct every aspect of a past calculation.

This fictional scenario illustrates preserved knowledge changes; not every operational table needs this complexity.

Common misconceptions and mistakes

• Treating created-at and updated-at timestamps as a complete bitemporal design. They do not necessarily preserve effective intervals or previous versions.
• Confusing the date a source system knew something with the date a downstream system recorded it.
• Overwriting the only historical version when accepting a correction.
• Assuming temporal tables make the data tamper-proof or supply a complete audit process.
• Using corrected future knowledge in a historical model evaluation without checking when it became available.

Business use

Bitemporal modeling helps when delayed information and retroactive corrections affect explanations or decisions. Start with concrete questions, such as what was effective on a service date and what the organization had recorded when it acted.

Consider the added storage, query complexity and user education. Define which system's recorded knowledge matters and how retention works. Test a normal change, a backdated correction and a correction to that correction. Preserve decision inputs or outputs where needed: data history alone cannot reproduce a business process.

People also ask

Does bitemporal mean two timestamps on every row?

Not simply. It means two defined time dimensions, often represented using two intervals and version history.

Can an effective date lie in the future?

Yes. A planned change can be recorded today but take effect later.

Is this the same as event sourcing?

No. Event sourcing records events as a source of state; a bitemporal model explicitly distinguishes effective and recorded time. A system can combine the ideas.

Check your understanding

A correction recorded on 10 March says an address changed on 1 March. Which address should an “as known on 6 March” query return for 5 March?

Answer: The address recorded before the correction, under the stated history. A query using current knowledge can return the corrected address for the same effective date.

Related terms

• Valid time
• Transaction time
• Temporal database
• System-versioned table
• Effective dating
• Audit trail
• Event sourcing
• Data provenance
• Point-in-time correctness

Learn next

Write three questions about one delayed correction and specify both dates for each. Then draw the effective and recorded intervals. Study temporal queries and boundary conventions before choosing a storage implementation.

More in Data engineering and architecture

• Big data maturity model - A big data maturity model is a framework for assessing how effectively an organization can manage and use large or complex datasets.
• Binary Decomposition - Binary decomposition divides one relation schema into two relation schemas whose attributes collectively cover the original schema.
• Bit-level parallelism - Bit-level parallelism is the ability to process multiple bits of information together within a machine operation.
• Bitmap index - A bitmap index is a database index that represents membership of rows in indexed values using sequences of bits.
• Botnet - A botnet is a network of compromised computers or other connected devices that can be directed to perform activities for an unauthorized operator.
• Bottom-up design - Bottom-up design, in the relational database context used here, develops a schema from detailed attributes and their dependency rules.

Sources

Primary sources: Martin Fowler, Bitemporal History, an original explanation of effective and recorded histories: https://martinfowler.com/articles/bitemporal-history.html ; IBM Db2 bitemporal table documentation: https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=tables-creating-bitemporal ; IBM business-time interval definitions: https://www.ibm.com/docs/en/db2/11.1.0?topic=tables-business-time-period . Fowler's relevant explanation and indexed IBM documentation reviewed; direct retrieval of the IBM bitemporal page was unavailable. Address and facilities examples are original illustrations.

Previous: Bit rate Next: Bitext word alignment