Bitemporal vs Unitemporal Data: A Field Guide
The Four-Dimensional Problem
The ticket came in at 2:47 AM. A hedge fund in Chicago was seeing phantom trades in their risk reports. Not fake trades — trades that had been correct yesterday, but were now displaying the wrong counterparty. The data hadn't changed. The relationship between the trade and the counterparty had changed, and the report was showing the current relationship applied to historical trades.
That was the moment I stopped treating temporal data modeling as a theoretical exercise.
Here's the reality: most databases are lying to you. They tell you what is true right now. But you rarely need what's true right now. You need what was true then, or what you thought was true then, or what the state of the system was at a specific point in time. That's the difference between bitemporal and unitemporal data, and understanding it will save you from the kind of 3 AM incident I just described.
In this guide, I'll walk through the difference between bitemporal and unitemporal data, show you how to implement temporal tables in PostgreSQL, and help you decide which approach your system actually needs.
The Two Clocks
Most people think time is one thing. In data modeling, it's two.
Unitemporal data tracks one time dimension. Usually that's the "valid time" — the period during which a fact is true in the real world. Think of it as the business time.
Bitemporal data tracks two. Valid time and transaction time — when the fact was recorded in the database.
These are not the same thing.
Let me give you a concrete example. Say you have a customer named Priya who lives in Mumbai. She moves to Pune on March 15. A unitemporal model captures this: her valid time for "Mumbai address" ends March 15, and "Pune address" begins. Simple.
But what if the system doesn't learn about the move until March 22? You update the record on March 22, but the new address was valid starting March 15. If you're only tracking valid time, you've lost the information that you didn't know about the move for a week. That's a unitemporal model — it answers "what was true?" but not "what did we know and when?"
Bitemporal data answers both.
This is the distinction that matters. Temporal Table Usage Scenarios - SQL Server walks through this exact scenario: the difference between "what the state of the database was" and "what the state of the world was." Both matter, but they serve different purposes.
Unitemporal: The SCD Type 2 That Runs Half the World
Let's be honest about what most people actually need.
If you're building a standard data warehouse with slowly changing dimensions, you're probably fine with unitemporal. Slowly Changing Dimensions and Temporal Databases makes this point well: SCD Type 2 is essentially a unitemporal model with training wheels. You add valid_from and valid_to columns, and suddenly you can track history.
Here's what that looks like in PostgreSQL:
sql
CREATE TABLE user_account (
user_id INTEGER PRIMARY KEY,
email TEXT,
status TEXT,
valid_from TIMESTAMPTZ NOT NULL DEFAULT NOW(),
valid_to TIMESTAMPTZ NOT NULL DEFAULT 'infinity',
UNIQUE (user_id, valid_from)
);
Every update becomes an insert. You close the old row, open a new one. The query pattern is simple: WHERE valid_from <= ? AND valid_to > ?.
But here's the thing I've learned from building these systems at SIVARO: unitemporal is a compromise, not a destination. It gives you history, but it doesn't give you accountability. If someone asks "when did we record this change?", you can't answer. You only know when the change became valid in the real world.
What Are Slowly Changing Dimensions? A Complete Guide breaks down the various SCD types, and I'd argue Type 2 is the most common because it's the least painful. You get history without a major schema overhaul.
But it breaks down in specific scenarios:
- Regulatory environments that require audit trails of data changes, not just business state
- Reconciliation where you need to explain why a report looked different yesterday
- Event sourcing where the sequence of state changes matters
For those, you need bitemporal.
Bitemporal: When the Record Itself Has a History
Bitemporal modeling means every row carries four time-related values:
valid_fromandvalid_to— business time (when the fact is true in reality)system_fromandsystem_to— transaction time (when you knew about it)
It's like having a versioned history of a versioned record. Meta, I know. But this is what What Is Temporal Data Modeling? How Databases Track ... calls the difference between "the real world" and "the recorded world." They're often the same. When they diverge, that divergence is information.
Here's the bitemporal version:
sql
CREATE TABLE user_account_bitemporal (
user_id INTEGER NOT NULL,
email TEXT,
status TEXT,
-- Business time
valid_from TIMESTAMPTZ NOT NULL,
valid_to TIMESTAMPTZ NOT NULL,
-- System time
system_from TIMESTAMPTZ NOT NULL,
system_to TIMESTAMPTZ NOT NULL,
PRIMARY KEY (user_id, valid_from, system_from)
);
Every business change creates a new row. Every recorded change creates a new version of that row. The combination gives you a two-dimensional history.
Why does this matter? Let me give you a real example from our work with a logistics company in 2025. They had a route optimization system that was producing different results for the same query run at different times of day. The problem wasn't the algorithm — it was that their product database had been updated with new supplier pricing, and the reports were showing current pricing applied to historical shipments.
With bitemporal data, you can ask two separate questions:
- What was the state of a shipment at time T? (valid time)
- What did we think the state was at time T, given the data available then? (system time)
That second question is the killer feature. It's how you audit your own data quality. It's how you prove to a regulator that you didn't retroactively change records.
Using Temporal Tables for Slowly Changing Dimensions shows how to extend SCD Type 2 into a bitemporal pattern. The extra columns aren't expensive — a few bytes per row. But the conceptual overhead is real.
How to Implement Temporal Tables in PostgreSQL (Without Losing Your Mind)
PostgreSQL doesn't have native temporal tables like SQL Server's system-versioned tables. You have to build it yourself.
The good news: you don't need a custom extension. The bad news: you need to be disciplined about your write patterns.
Here's the approach that works for us at SIVARO:
1. Use a valid_during range column
Postgres supports range types natively, and they solve the gap problem (no, not that gap — the one between valid_to and the next valid_from):
sql
CREATE TABLE customer (
id INTEGER PRIMARY KEY,
name TEXT,
address TEXT,
valid_during TSRANGE NOT NULL,
EXCLUDE USING gist (id WITH =, valid_during WITH &&)
);
The exclusion constraint prevents overlapping ranges. That's your invariant. No double-booking time.
2. Handle the update as an insert
You can't UPDATE a temporal row. You close the old one and insert the new one. Wrap it in a transaction:
sql
BEGIN;
UPDATE customer
SET valid_during = TSRANGE(lower(valid_during), NOW())
WHERE id = 42 AND upper(valid_during) = 'infinity';
INSERT INTO customer (id, name, address, valid_during)
VALUES (42, 'Priya', 'Pune', TSRANGE(NOW(), 'infinity'));
COMMIT;
This is the core pattern. Tim Mitchell's article calls this the "closed-open" convention, and it's the right call. The current row has valid_to = infinity, which makes queries simpler.
3. Add the system time for bitemporal
For bitemporal, you need a second range. Same pattern, different column:
sql
CREATE TABLE customer_bitemporal (
id INTEGER NOT NULL,
name TEXT,
address TEXT,
valid_during TSRANGE NOT NULL,
system_during TSRANGE NOT NULL,
EXCLUDE USING gist (id WITH =, valid_during WITH &&),
EXCLUDE USING gist (id WITH =, system_during WITH &&)
);
Now every change to the business state creates a new row, and every recording of a change creates a new version.
4. Write the time-travel query
The query pattern changes depending on what you're asking:
sql
-- What was the state at a point in time?
SELECT * FROM customer_bitemporal
WHERE id = 42
AND valid_during @> '2026-06-01'::timestamptz;
-- What did we know at a point in time?
SELECT * FROM customer_bitemporal
WHERE id = 42
AND system_during @> '2026-06-01'::timestamptz;
The first query uses business time. The second uses system time. They're different answers, and both are correct.
5. Don't forget the audit log
The exclusion constraints handle overlap prevention, but they don't handle who changed what. Add a changed_by column. Trust me on this — you'll need it when someone asks "who updated this customer's address?"
The Most Common Mistake: Picking One Temporal Dimension
Here's the contrarian take: most teams don't need bitemporal data. And the teams that do need it often don't realize it until it's too late.
I've seen three failure modes:
Failure mode 1: Unitemporal when you need accountability. A healthcare startup in Berlin built a unitemporal patient record system. When an audit revealed that a diagnosis had been changed, they couldn't prove whether the change was a correction or a falsification. They couldn't answer "what did we know and when?" Unitemporal failed them.
Failure mode 2: Bitemporal when you need simplicity. A retail analytics company went all-in on bitemporal modeling for their product catalog. Every SKU had two time dimensions. Their queries became complex, their performance tanked, and their analysts stopped trusting the data because they didn't understand the semantics. Bitemporal was overkill — they were the source of truth for their own catalog, so there was no "discovery lag" to track.
Failure mode 3: Temporal when you need neither. I've seen teams add valid_from/valid_to to tables that were append-only event logs. The event timestamp was the valid time. Adding a second time column created confusion and performance overhead for zero benefit.
The rule I now use: if you need to answer "what was the state?" use unitemporal. If you need to answer "what did we know and when?" use bitemporal. If you don't need either, don't add the columns.
How to Choose Temporal vs Wall Clock Time
There's a deeper question hiding underneath this: should you use temporal modeling at all, or just use wall clock timestamps?
Here's the thing. A timestamp is a point. Temporal modeling is a range. They answer different questions.
- "When did the customer place the order?" → timestamp
- "How long was the customer in 'pending' status?" → temporal range
- "What was the customer's status on June 1?" → temporal query
- "When did we record that the customer's status changed?" → system time
I've seen teams try to answer temporal questions with timestamps by adding columns like updated_at and then trying to reconstruct history from SELECT statements. That's a join nightmare and a correctness trap. Temporal Table Usage Scenarios lists a bunch of scenarios where temporal tables are the right tool — data auditing, point-in-time analysis, and slowly changing dimensions are the big three.
But I've also seen teams use temporal tables when a simple created_at column was sufficient. If you never need to answer "what was the state at time T?", you don't need temporal modeling.
The heuristic I use: if you have a WHERE clause that references updated_at, you're probably doing something temporal. If you have a WHERE clause that references valid_from <= ? AND valid_to > ?, you're doing temporal modeling. The second is more honest about what you're trying to do.
Bitemporal vs Unitemporal Data: The Mental Model
Let me give you a way to think about this that I've used with clients.
Unitemporal is a camera. It takes a picture of reality at a moment in time. If reality changes, you take a new picture. But you can't tell which picture was taken first, or whether the camera was adjusted.
Bitemporal is a video with metadata. Every frame has a timestamp. And the recording device logs when each frame was captured. You can rewind to any moment and see exactly what the camera saw — and exactly what the camera had captured at that moment.
The TDWI article frames this as the difference between "the time something happened" and "the time the database recorded it." That's the cleanest way to explain it to stakeholders who aren't data engineers.
When you're designing a system, ask yourself: does anyone need to know both?
- A financial reconciliation system? Yes.
- A customer address book? Probably not.
- A supply chain tracking system with multiple parties reporting status? Absolutely.
- An analytics dashboard showing current KPIs? No.
The dev.to article makes a good point about this: the more distributed your data sources, the more likely you are to need bitemporal modeling. When multiple systems report on the same entity, the "discovery lag" between real-world changes and system updates becomes significant.
A Workflow That Works
Here's what I recommend after building this stuff in production:
Start unitemporal. If you're building something new, model the business time first. Get the exclusion constraints right. Get the closed-open convention right. This is the foundation.
Add system time only when you hit a concrete requirement. Not "we might need it someday." A regulator asking for an audit trail. A customer complaining that a report changed retroactively. An internal investigation into data quality. That's when you add the second dimension.
Use a library or framework if you can. We built a small internal tool at SIVARO that generates the temporal CRUD operations from a schema definition. It's maybe 200 lines of SQL generation. It's not hard, but it's repetitive, and repetition is where bugs creep in.
Test the time travel queries. Write tests that simulate a delayed update — a fact that becomes valid on day 1 but is only recorded on day 5. Verify that both the business-time query and the system-time query return the correct results. This is the test that catches 90% of temporal modeling bugs.
Don't use timestamps for temporal queries. If you find yourself writing WHERE updated_at BETWEEN ? AND ? to figure out what changed in a time window, you're doing it wrong. Use range types and temporal queries. Your future self will thank you.
FAQ
What's the difference between valid time and transaction time?
Valid time is when a fact is true in the real world. Transaction time is when the database records that fact. For example, a customer moves on June 1 (valid time), but you don't update the record until June 5 (transaction time). Both are correct; they answer different questions.
Can I implement temporal tables in PostgreSQL without extensions?
Yes. PostgreSQL doesn't have native system-versioned temporal tables like SQL Server, but you can build them with TSRANGE columns and exclusion constraints. It requires discipline in your write patterns — every update is an insert — but it's completely doable with standard PostgreSQL features.
When should I choose bitemporal over unitemporal data?
Choose bitemporal when you need to audit the history of your data itself, not just the history of the real world. Regulatory compliance, financial reconciliation, and multi-system data integration are the common use cases. If you only need to answer "what was the state at time T?", unitemporal is enough.
What are the performance costs of temporal tables?
Bitemporal tables grow faster because every business change creates a new row, and every recorded change creates a new version. Query performance can degrade if you don't index the range columns properly. GiST indexes on the range types are essential. In practice, we've seen a 2-3x query slowdown compared to non-temporal tables, which is acceptable for most use cases.
How do temporal tables relate to slowly changing dimensions?
SCD Type 2 is essentially a unitemporal model with valid_from/valid_to columns. Bitemporal extends this with system_from/system_to columns, which track when the database learned about each change. Tim Mitchell's article has a good walkthrough of this pattern.
Do I need bitemporal for event sourcing?
Not necessarily. Event sourcing stores facts as an append-only log, which gives you a system-time history by construction. But if your events contain "effective dates" — when a fact becomes true in the real world — you're combining both temporal dimensions. Bitemporal modeling gives you a clean way to query that combined history.
The Bottom Line
Bitemporal and unitemporal data aren't competing approaches. They're different tools for different questions.
Unitemporal data tells you what the world looked like. Bitemporal data tells you what the world looked like and what you knew about it at any point. Most systems need the first. Some need the second. Fewer still need both, but for those, bitemporal modeling is the only honest way to answer the questions regulators, auditors, and customers will ask.
The key is knowing which one you're building before you build it. The 3 AM phone call I started with? It happened because a system that should have been bitemporal was built unitemporal. The reconciliation reports were showing current data applied to historical events, and nobody could explain why.
That's the cost of getting temporal modeling wrong. It's not a performance problem or a schema problem. It's a trust problem. Once your stakeholders stop trusting the data, getting that trust back is nearly impossible.
Build it right the first time. Ask the two questions. And if you can't answer "what did we know and when?" with your current schema, you know what to do.
Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.