Bitemporal SQL Interview Questions: Effective Time, Recorded Time, and Corrections

A contract's February fee was reported as $100. In March, a correction says it should have been $120 for part of February. “What was the fee on February 20?” now has two legitimate answers: what the system believed then, and what its corrected history says today.

Bitemporal SQL interview questions require both an effective-time cutoff and a recorded-time cutoff. Preserve earlier assertions, select the intended knowledge snapshot, and then locate the business interval. This article is for SQL and data-engineering candidates.

Evidence boundary: database documentation establishes native features. The ledger, checks, and design recommendations below are original practice, not candidate reports or employer-specific questions. Portable predicates were executed in SQLite; native Db2 and PostgreSQL features were documentation-reviewed.

What are the two time axes?

Effective time, also called business or valid time, describes when an assertion applies in the modeled world. Recorded time describes the interval during which the system retains that assertion as its accepted version. A retrospective correction changes an earlier business interval while becoming recorded later.

Official IBM behavior: Db2's BUSINESS_TIME period represents application-defined validity, uses inclusive-start/exclusive-end boundaries, and can optionally enforce nonoverlapping business periods for a key. See the BUSINESS_TIME documentation. IBM also documents queries applying BUSINESS_TIME and SYSTEM_TIME together in bitemporal querying.

Our manual ledger uses names effective_from, effective_to, recorded_from, and recorded_to. These names describe our contract; they are not a claim that every engine assigns identical meanings to similarly named columns.

The distinction matters when a report asks, “Why did we charge that amount?” A corrected-history query explains today's best assertion. An as-known query reconstructs the stored assertion at a named past cutoff. Neither alone proves what a customer-facing service displayed, because caches and publication delays introduce additional evidence requirements.

Build a small correction ledger

This original fixture models contract K7. Fees are USD per month; the exercise queries the applicable rate, not a prorated invoice. Effective intervals use calendar dates. Recorded instants are UTC and have second precision. Every interval is half-open, [start, end), and NULL recorded_to means open-ended.

On January 2 at 10:00 UTC, the system records a $100 fee effective January 1 through April 1. On March 5 at 10:00 UTC, it accepts a correction: February 15 through March 1 should be $120. The original assertion must remain queryable under an earlier knowledge cutoff.

After correction, the complete ledger contains four rows:

Row and monthly fee	Effective interval	Recorded interval
R1: $100	Jan 1–Apr 1	Jan 2 10:00–Mar 5 10:00
R2: $100	Jan 1–Feb 15	Mar 5 10:00–open
R3: $120	Feb 15–Mar 1	Mar 5 10:00–open
R4: $100	Mar 1–Apr 1	Mar 5 10:00–open

All dates are in 2026. R1 preserves the old complete assertion. R2–R4 describe the newly accepted business history. Three current rows are necessary because the corrected middle span differs from both surrounding spans.

Do not truncate R1's business end to February 15. That would change what the old snapshot says about late February and March. Close its recorded interval instead. Preserving history means retaining the previous assertion's payload and effective interval, not simply keeping its identifier.

Predict the answers before writing SQL

A good interview fixture needs different questions with the same effective date, plus boundary cases that expose accidental double inclusion.

Effective date	Knowledge cutoff, UTC	Expected rate
Feb 20	Feb 28 12:00	$100 from R1
Feb 20	Mar 6 12:00	$120 from R3
Feb 15	Mar 6 12:00	$120 from R3
Mar 1	Mar 6 12:00	$100 from R4
Feb 20	Mar 5 10:00 exactly	$120 from R3
Jan 1	Jan 1 12:00	No recorded assertion
Apr 1	Mar 6 12:00	No effective assertion

The January case is not zero dollars. The ledger did not yet contain an assertion at that knowledge cutoff. Likewise, April 1 is outside the original exclusive end. Specify how an API or report represents these missing answers instead of silently filling them with a fee.

The table also illustrates a future-effective assertion known earlier: R1 can answer a February question using January's knowledge snapshot. Whether forecasting from such assertions is useful depends on the requested analysis; bitemporal storage itself does not prohibit that query.

Write the two-cutoff predicate

For our application-managed fee_history table, use explicit predicates on both axes:

SELECT row_id, contract_id, fee_usd,
       effective_from, effective_to,
       recorded_from, recorded_to
FROM fee_history
WHERE contract_id = :contract_id
  AND effective_from <= :effective_date
  AND :effective_date < effective_to
  AND recorded_from <= :known_at
  AND (recorded_to IS NULL OR :known_at < recorded_to);

The recorded predicate selects the accepted snapshot; the effective predicate selects the applicable interval within that snapshot. SQL need not physically evaluate them in that order. The explanation concerns eligibility, not a mandated execution plan.

Bind contract_id='K7', effective_date='2026-02-20', and a normalized UTC known_at. In the SQLite verification, fixed-format ISO strings make comparisons meaningful. In another database, use appropriate date/timestamp types and parameter bindings rather than assuming arbitrary text dates sort correctly.

A query filtering only effective time returns R1 and R3 for February 20 from the complete ledger. A query filtering only recorded time returns R2, R3, and R4 at March 6. Neither identifies the requested single assertion.

Adding ORDER BY recorded_from DESC LIMIT 1 conceals those mistakes. It cannot repair an omitted business predicate, and it removes the ability to ask an earlier as-known question. When the contract requires one answer, validate that eligibility returns at most one row.

What must the correction transaction write?

For this fixture, capture the accepted correction instant once. In one atomic operation, close R1's recorded interval at March 5 10:00 and insert R2, R3, and R4 with that same recorded start. Keep the original effective span and $100 payload in R1.

This is an original application-managed design, not instructions for manually updating engine-generated system-period columns. Native temporal tables have their own supported write operations. Do not combine the manual ledger algorithm with a database-managed history table without understanding its rules.

Validate the correction before closing anything: it belongs to K7, has a positive interval, falls inside the expected active span, and carries a recognized source revision. Lock or otherwise coordinate the affected key, and reject a write if the expected active version has changed.

A read-then-write application check without database coordination is insufficient under concurrent corrections. Two writers might both split the same old assertion and publish incompatible current spans. Choose a transaction/isolation or locking strategy that the target engine can enforce, and make conflict retries reload the current history.

Record an idempotency identity separately, such as source document plus revision. A repeated identical correction should not create another recorded version. A repeated identity with changed content is a conflict requiring an explicit policy. Do not settle that conflict using whichever request arrived last.

Which overlap is actually invalid?

Think of each row as a rectangle: one effective interval crossed with one recorded interval. Under our single-valued contract, two rows for K7 are ambiguous only when their rectangles overlap on both axes.

R1 and R3 overlap in effective February time, but their recorded periods merely touch at March 5 10:00. They are valid historical alternatives. R2 and R3 coexist in recorded time, but their effective periods merely touch at February 15. They are valid adjacent spans.

An extra current $130 row effective February 18–25 would overlap R3 on both axes. February 20 at March 6 would then return two rates. That is a data integrity failure, even if a query arbitrarily chooses one.

Official PostgreSQL capability: range types and exclusion constraints can express nonoverlap, including combined equality and range conditions with suitable operator classes. See range types and constraints. A constraint enforcing only effective nonoverlap across all history would reject legitimate earlier assertions. Model the actual rectangle rule or restrict the constraint to the intended snapshot scope.

Nonoverlap does not imply completeness. A mistaken split that leaves February 14 uncovered may contain no overlaps yet still violate a requirement for continuous coverage. Test adjacency, union coverage, and unchanged outside spans separately. Not every entity needs continuous coverage; attach that invariant to the business contract.

Does a native temporal table solve both clocks?

Official Microsoft behavior: SQL Server system-versioned temporal tables preserve row versions in current/history tables. Their system-period times use the transaction's begin time in UTC. That clock should not be described as source-event time or guaranteed commit time. See temporal table overview.

System versioning can support the recorded-history side, but the business validity model still needs its own fields and rules. Conversely, a business-valid SCD Type 2 dimension can answer a corrected timeline without retaining every earlier belief. Compare the separate SCD Type 2 history-repair exercise.

IBM documents native bitemporal queries using both period specifications. Confirm the chosen engine, version, table definition, and clock semantics before offering native syntax. The portable query above intentionally exposes the logical predicates without claiming every SQL dialect implements the same feature.

For historical model evaluation, also ask when the value reached the intended serving path. The point-in-time feature-join exercise addresses that availability boundary. A warehouse recorded timestamp is useful evidence, but it is not automatically the time an online decision could access the value.

Verify corrections and reconstructability

The local SQLite checks execute the two-cutoff query against the four-row ledger. They test the seven answer cases, a cutoff just before correction, the missing-key case, and a deliberately inserted ambiguous rectangle. A separate transaction check forces failure after closing R1 and confirms rollback leaves the original active assertion intact.

The fixture also checks that replaying the accepted correction identity leaves the four-row result unchanged. This demonstrates the example's chosen replay policy; it does not validate production concurrency, native temporal enforcement, or a complete migration process.

A strong production answer adds retained source revisions and the code version that interpreted them. Reconstructing stored rows is different from recreating a past report with changed transformations. Materialized reports and caches may need invalidation or a versioned rebuild when corrected business spans change.

For larger histories, inspect representative plans for current and historical cutoffs separately. The two access patterns may favor different indexes. Avoid claiming that one composite index necessarily serves both inequalities efficiently. Performance evidence should preserve the same query contract and conflict checks.

Practice explaining both dates

Question	Bitemporal follow-up
Merge customers and preserve history	Preserve earlier identity assertions after a correction.
Design a Timestamped Cloud Credit Ledger with Rebates, Merges, and History	Separate effective changes from their recorded history.
Design and Debug a Point-in-Time Backtest Data Pipeline	Define which knowledge cutoff supports a replay.
Model Data for Analytics, Reporting, and Applications	Label corrected and as-known reporting explicitly.
Design an idempotent SQL ETL for late data	Make correction redelivery leave history unchanged.

Choose one data-engineering question, write an effective date and a knowledge cutoff, then predict the selected assertion. Your answer should explain why a later correction changes today's view without rewriting what the earlier snapshot believed.

Sources and Further Reading

• IBM Db2: BUSINESS_TIME period — application validity and interval boundaries.
• IBM Db2 12.1: querying bitemporal data — querying both temporal axes.
• Microsoft: SQL Server temporal table overview — system-managed history and transaction-based period times.
• PostgreSQL: range types — ranges and nonoverlap constraints.

Evidence checked October 3, 2026. Native database capabilities are documentation-based; local execution covers only the disclosed synthetic SQLite exercise.