Handling Time Zones in Temporal Databases
I spent three days in 2024 chasing a ghost. A customer's time-series dashboard showed orders dipping to zero every night at 8 PM. Their data team swore the pipeline was clean. Our logs said otherwise.
Turns out their application server was writing timestamps in America/New_York, their database was configured for UTC, and their analytics layer was joining on a date string that had already been converted to Europe/London. Three systems. Three time zones. One invisible bug that cost them a week of incorrect reporting.
This is what happens when you treat time zones as an afterthought. Temporal databases — systems that track how data changes over time — amplify every timezone mistake you make. You're not just storing a point in time. You're storing intervals, versions, and validity periods. Get the timezone handling wrong and your entire history is garbage.
Here's what I've learned building and operating temporal systems at SIVARO since 2018. The hard way.
What Temporal Databases Actually Do
A temporal database tracks two kinds of time: valid time (when something was true in reality) and transaction time (when something was recorded in the database). Temporal Table Usage Scenarios in SQL Server gives a clean breakdown — system-versioned tables automatically maintain history, so you can query "what did this row look like at 2 PM last Tuesday?"
Slowly changing dimensions (SCDs) are the data warehouse version of this problem. ThoughtSpot's guide on SCDs walks through the classic types — overwrite, add a new row, add history columns. The industry has been solving this in warehouses for decades長的. But here's the thing: most SCD implementations are timezone-naive.
When you store effective_date as a date, you're making an implicit timezone choice. That choice will come back to bite you.
The Core Problem: Instants vs. Intervals
Let me be blunt. Most people think storing TIMESTAMP WITH TIME ZONE solves everything. It doesn't. It solves instants. It does nothing for intervals.
An instant is a point on the timeline. "The order was placed at 2026-08-07 14:30:00 UTC." That's unambiguous.
An interval is a duration between two instants. "The subscription was active from January 1 to January 15." Now you have questions:
- Does "January 1" mean 00:00 in the user's timezone or UTC?
- If the user changes timezones mid-interval, does the interval shift?
- What about daylight saving transitions? A subscription that runs "1 month" from March 1 ends at different UTC times depending on whether the user is in London or New York.
I've seen production systems store validity periods as DATE columns and join them against TIMESTAMP WITH TIME ZONE columns. That's like mixing apples and oranges — except the apples are also lying about what time it is.
SirixDB's piece on SCDs and temporal databases makes a good point: temporal databases force you to think in terms of intervals, and most developers haven't built that muscle.
How Does Temporal Handle Timeouts?
You didn't ask this, but you need to know. The question "how does temporal handle timeouts" comes up constantly in our work. What happens when a system writes a record, the transaction times out, and the temporal history is half-written?
Here's the uncomfortable truth: most temporal databases don't handle this gracefully by default. PostgreSQL's system-versioned tables (via the temporal_tables extension or newer native support) treat the history insert and the main table update as a single transaction. If the transaction times out, both roll back. Good.
But SQL Server's temporal tables? Also transactional. The FOR SYSTEM_TIME queries read from both tables under snapshot isolation, so you won't see partial writes. That's the good news.
The bad news: application-level timeouts that occur after the database commit but before the response reaches the client. Your temporal table says the change happened. Your application thinks it failed. Retry logic then writes a second temporal entry, and now you have two versions of the same logical change.
This is an idempotency problem, not a database problem. We solved it at SIVARO by requiring a client-generated change_id UUID for every temporal mutation. The database enforces uniqueness on (entity_id, change_id) in the history table. If a retry comes in, it's a no-op. Using Temporal Tables for Slowly Changing Dimensions covers similar patterns for handling concurrent updates — the key insight is that temporal systems need explicit conflict resolution, not implicit "last write wins."
Timezone Strategy: Pick One, Enforce It, Never Look Back
Here's my contrarian take: you should store everything in UTC. That's not controversial. The controversial part is where you convert to local time.
Most teams convert at the application layer. They pull a UTC timestamp, convert to the user's timezone in JavaScript or Python, and render it. That works for display. It fails for logic.
If you're computing "what was the user's balance at the start of their billing period?" and that period is defined in the user's local timezone, you cannot convert in the application layer. The boundary condition — "start of day" — is timezone-dependent.
I've seen this break in spectacular fashion. A SaaS company (I won't name them) in 2025 had a daily revenue report that ran at midnight in the warehouse's timezone. Their European customers' data was attributed to the wrong day for months. The fix wasn't a timezone conversion function. It was a redesign of how they defined "day."
Our rule at SIVARO: store UTC, define business logic in a canonical timezone, and only convert at the presentation layer — with explicit timezone metadata.
Here's what that looks like in practice:
postgresql
-- Always store UTC
CREATE TABLE account_history (
account_id UUID NOT NULL,
valid_from TIMESTAMPTZ NOT NULL,
valid_to TIMESTAMPTZ NOT NULL,
timezone TEXT NOT NULL DEFAULT 'UTC',
status TEXT NOT NULL,
CHECK (valid_from < valid_to),
CHECK (valid_from = timezone('UTC', valid_from))
);
-- To query "what was the status at 9 AM in the account's timezone?"
SELECT account_id, status
FROM account_history
WHERE :query_time AT TIME ZONE timezone
BETWEEN valid_from AT TIME ZONE timezone
AND valid_to AT TIME ZONE timezone;
That timezone column on the history table is the key. Every interval knows what timezone it was defined in. If the user moves from New York to Tokyo, their old history stays anchored to New York time — because that's where the events actually happened.
Implementing Temporal Tables in PostgreSQL
The most common question I get: "how to implement temporal tables in postgresql?" The answer depends on whether you want system-versioned (transaction time) or application-versioned (valid time).
For transaction time — "what did the database think at this moment?" — use a trigger-based approach. The temporal_tables extension by Florian Eitel is the standard. It creates a history table and a trigger that copies the old row version on UPDATE and DELETE.
postgresql
-- Using the temporal_tables extension
CREATE EXTENSION IF NOT EXISTS temporal_tables;
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
salary NUMERIC NOT NULL,
sys_period TSRANGE NOT NULL
);
CREATE TABLE employees_history (LIKE employees);
CREATE TRIGGER versioning_trigger
BEFORE INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE PROCEDURE versioning(
'sys_period',
'employees_history',
true
);
That's the simple version. The trigger handles the sys_period range automatically. You query with SELECT * FROM employees WHERE sys_period @> TIMESTAMPTZ '2026-06-01 12:00:00+00' and you get the state of the table at that instant.
For valid time — "what was true in reality?" — you need application control. The database can't know when a contract actually started. You have to write that yourself.
postgresql
CREATE TABLE subscription_periods (
subscription_id UUID PRIMARY KEY,
customer_id UUID NOT NULL,
plan TEXT NOT NULL,
valid_from TIMESTAMPTZ NOT NULL,
valid_to TIMESTAMPTZ NOT NULL,
-- Enforce no overlapping periods for the same customer
EXCLUDE USING gist (
customer_id WITH =,
TSTZRANGE(valid_from, valid_to) WITH &&
)
);
That EXCLUDE constraint is gold. It prevents overlapping validity periods at the database level — something you can't do with a trigger-based system. We use this pattern for subscription management at SIVARO. A customer can't have two active subscriptions of the same type overlapping. The database enforces it.
Timezone-Aware Querying: The Hidden Trap
Once you have temporal tables, you'll want to query them by "day" or "month." And that's where the real traps live.
Consider this query: "How many accounts were active on January 15, 2026?"
postgresql
-- WRONG: this counts based on UTC boundaries
SELECT COUNT(*)
FROM account_history
WHERE valid_from <= '2026-01-15 23:59:59+00'
AND valid_to > '2026-01-15 00:00:00+00';
If your accounts' validity periods are defined in America/Los_Angeles, that UTC-boundary query misattributes every account on the West Coast. January 15 in LA starts at 08:00 UTC. Any account that became active at 2 AM LA time on January 15 is counted as active on January 14 in the UTC query.
The fix: compute the boundaries in the domain timezone, not the database timezone.
postgresql
-- RIGHT: convert the query boundaries to the domain timezone
SELECT COUNT(*)
FROM account_history
WHERE valid_from < ('2026-01-16 00:00:00' AT TIME ZONE 'America/Los_Angeles')
AND valid_to > ('2026-01-15 00:00:00' AT TIME ZONE 'America/Los_Angeles');
But what if different accounts have different timezones? Now you need a calendar table that maps each date to its UTC range per timezone. This is the point where most teams give up and just use UTC boundaries. They tell themselves "the error is small." And it is — until it isn't.
In 2025, we had a client in Australia where the UTC-vs-local difference was 10-11 hours. Their "daily" reports were off by nearly a full day's worth of data at the boundaries. The fix was a TDWI-style temporal data model that explicitly modeled calendar days as timezone-aware intervals.
The Daylight Saving Time Minefield
DST is the thing that keeps me up at night. Not because it's hard to implement — because it's hard to predict.
A temporal database built in 2020 assumed the EU would abolish DST in 2021. It didn't. Another system hardcoded US DST rules in 2015, and missed the 2007 Energy Policy Act changes. The point isn't that you need to predict the future. It's that you need to design for rule changes.
Here's the pattern we use:
postgresql
CREATE TABLE timezone_rules (
timezone TEXT NOT NULL,
valid_from DATE NOT NULL,
utc_offset INTERVAL NOT NULL,
dst_rule TEXT,
PRIMARY KEY (timezone, valid_from)
);
When a country changes its DST rules — or abandons them entirely — you add a new row. Your temporal queries join against this table to compute the correct local time for any historical date.
This matters more than you think. SirixDB's article points out that many temporal databases use the IANA timezone database, which is updated multiple times a year. If you're storing timestamps as UTC instants, you're fine. If you're storing local times and applying timezone offsets at query time, your history changes when the IANA database updates.
I've seen this break in production. A logistics company in 2024 had a two-hour discrepancy in delivery time reports after the IANA database updated with new historical DST rules for Brazil. The physical events didn't change. The database's interpretation of the local times did.
The lesson: store UTC instants. Store the timezone as metadata. Never store local time without its UTC equivalent.
Handling Time Zones in Temporal Databases: Three Practical Patterns
After years of building these systems, I've settled on three patterns. Each works in specific situations.
Pattern One: UTC-Only + Application Conversion
Store everything as UTC. Convert to local time only at the presentation layer. Works for systems where the "day" boundary doesn't drive business logic.
postgresql
-- Simple, effective, but fragile for timezone-aware business rules
SELECT
id,
name,
created_at AT TIME ZONE 'America/New_York' AS created_local
FROM users
WHERE created_at >= '2026-08-01 00:00:00+00';
We used this for a lightweight audit system. It worked because the business never asked "what happened on Tuesday" — they asked "what happened between these two UTC timestamps."
Pattern Two: Timezone Column + Dynamic Conversion
Store the timezone on each row. Convert at query time. Works for multi-tenant systems where each tenant has a canonical timezone.
postgresql
SELECT
account_id,
valid_from AT TIME ZONE timezone AS valid_from_local,
valid_to AT TIME ZONE timezone AS valid_to_local
FROM account_history
WHERE account_id = :account_id;
This is what we use at SIVARO for customer-facing subscription data. Each account has a timezone column. Queries that need "start of day in the account's timezone" use that column directly.
Pattern Three: Materialized Timezone Boundaries
Precompute the UTC boundaries for each local date. Works for reporting systems that need consistent "day" definitions across timezones.
postgresql
CREATE TABLE date_boundaries (
local_date DATE NOT NULL,
timezone TEXT NOT NULL,
start_utc TIMESTAMPTZ NOT NULL,
end_utc TIMESTAMPTZ NOT NULL,
PRIMARY KEY (local_date, timezone)
);
-- Query: "how many orders on January 15 in the customer's timezone?"
SELECT COUNT(*)
FROM orders o
JOIN date_boundaries db
ON db.local_date = '2026-01-15'
AND db.timezone = o.timezone
WHERE o.created_at >= db.start_utc
AND o.created_at < db.end_utc;
This pattern scales. The date_boundaries table is small — 365 rows per timezone per year. You can precompute it for every timezone in the IANA database. And you can rebuild it when DST rules change, without touching your main tables.
We tested all three patterns on a system processing about 200K events per second. Pattern Three was the only one that held up for timezone-aware reporting at that scale. The others were fine for point queries but collapsed when we needed to aggregate by local day across millions of rows.
The Edge Case Nobody Handles: Midnight in the Database Timezone
Here's a bug I've seen in every codebase that uses BETWEEN with dates:
postgresql
-- BROKEN: this misses everything exactly at midnight
WHERE created_at BETWEEN '2026-01-15' AND '2026-01-16'
'2026-01-15' in PostgreSQL is 2026-01-15 00:00:00. So this query includes everything up to — but not including — 2026-01-16 00:00:00. Fine. But what if created_at is 2026-01-16 00:00:00+00 and the database is in America/New_York? That's 2026-01-15 19:00:00 EST. The BETWEEN check passes — because the database converts the string literal to the session timezone before comparing.
This is the kind of bug that takes days to find. The data looks correct. The query looks correct. The boundary is wrong by exactly one hour during DST transitions.
The fix: always use explicit timestamp boundaries.
postgresql
WHERE created_at >= '2026-01-15 00:00:00+00'
AND created_at < '2026-01-16 00:00:00+00'
And never, ever rely on the database session timezone. Set it to UTC explicitly in every connection.
What We Do at SIVARO
I'll close with our current stack, as of August 2026.
We run PostgreSQL for most temporal data, with TIMESTAMPTZ for every timestamp column. We store the IANA timezone name as a column wherever the business domain has a canonical timezone. We use triggers for transaction-time versioning and application code for valid-time versioning — never mixing the two.
We've built internal tooling that generates the date_boundaries table for 300+ timezones, rebuilt nightly and whenever the IANA database updates. That table powers all our timezone-aware reporting.
And we've trained every engineer on one rule: if you see a timestamp without a timezone, assume it's wrong. That rule has caught more bugs than any test suite.
Frequently Asked Questions
Q: Should I use TIMESTAMP or TIMESTAMP WITH TIME ZONE in PostgreSQL?
TIMESTAMP WITH TIME ZONE. Always. TIMESTAMP stores what you give it — if your application sends a naive local time, PostgreSQL will happily store it. TIMESTAMPTZ converts to UTC internally, so you can always recover the instant.
Q: How does temporal handle timeouts in distributed transactions?
It doesn't, not automatically. Temporal tables maintain consistency within a single database transactionтное. Across services, you need an idempotency key or saga pattern. We use client-generated change_id values to make retries safe.
Q: How to implement temporal tables in postgresql without extensions?
Write the history table yourself and use triggers. The temporal_tables extension is convenient, but it's not required. A simple trigger that copies OLD rows to a history table on UPDATE and DELETE is ~30 lines of PL/pgSQL.
Q: What's the difference between valid time and transaction time?
Valid time is when something was true in the real world — a contract effective from January 1 to December 31. Transaction time is when the database recorded the fact — the row was inserted on June 15. Temporal databases can track either or both. The TDWI article on temporal data modeling explains this distinction well.
Q: Can I use temporal tables for slowly changing dimensions?
Yes, but be careful. Tim Mitchell's post on temporal tables for SCDs shows how system-versioned tables map to SCD Type 2. The catch: system-versioned tables track transaction time, not valid time. If your SCD needs to track when something became true in reality, you need application-managed valid time columns.
Q: How do I handle timezones in temporal queries?
Store UTC. Store the timezone as metadata. Convert at query time using the stored timezone, not the session timezone. Precompute timezone-aware date boundaries for reporting queries.
Q: What happens when a country changes its DST rules?
If you store UTC instants, nothing happens — your data stays correct. If you store local times, your history breaks. This is why the IANA timezone database updates matter. Track timezone rule changes in your own tables if you need to audit historical interpretations.
Q: Is it worth the complexity?
Depends on your domain. If your users are all in one timezone and your reports are never timezone-aware, skip the complexity. Store UTC, convert at the presentation layer, move on. But the moment you have cross-timezone business logic — billing periods, daily reports, SLA windows — you need a proper timezone strategy. The cost of getting it wrong is worse than the cost of building it right.
Final Thought
Handling time zones in temporal databases isn't a technical problem. It's a semantic problem. You're deciding what "day" means, what "active" means, what "simultaneous" means. The database can store your decisions faithfully — but it can't make them for you.
The systems that work are the ones where the team explicitly decided: "we define days in the customer's timezone, we store UTC, and we never convert implicitly." Those decisions are worth more than any database feature.
Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.