Timezones, Windows & Time Bucketing

Limited availability

Time windows and time bucketing (this page) are rolling out per source engine, not all at once.

• PostgreSQL — supported today.
• MySQL, SQL Server, Oracle, Db2 — support is still being hardened and validated per engine. Until an engine is verified, DataRecs fails loud with an explicit error rather than returning a silently-incorrect bucket — it never guesses.
• Interpreting naive (no-offset) timestamp columns via a connection’s source_timezone is not yet available as a self-serve API/CLI/Terraform field. If a naive-timestamp source blocks you today, contact support.

Check the changelog for per-engine rollout updates. The design and correctness guarantees below apply to every engine as it becomes available — they are proven ahead of each engine’s rollout, not after.

Reconciling time-series data is where “the same data” most often looks different between two systems. A sale recorded at 2026-03-29T00:30:00Z belongs to 29 March in UTC but to 30 March in Australia/Sydney. Group by day in the wrong timezone and two sources that hold identical data will disagree at every boundary.

This page explains how DataRecs makes time comparisons deterministic: the single timezone a reconciliation buckets in, how that timezone is resolved, what a time window includes, and how the platform stays correct across daylight-saving transitions. The design is engine-agnostic — see the availability note above for which engines it’s live on today.

What “temporal handling” covers

Two things in a job use time:

• Time windows — a filter that restricts a run to rows in a date/time range (for example, “yesterday’s transactions”). A window has a start and an end instant.
• Time bucketing — grouping by a time dimension at a granularity (hour, day, week, month) so you compare, say, the daily total from each source. A temporal dimension is a group-by key computed from a timestamp column.

Both depend on one decision: which timezone defines the boundaries? Midnight, and therefore which day a row falls in, is a property of a timezone, not of the raw timestamp.

The bucketing timezone and how it is resolved

Every reconciliation buckets in exactly one timezone, applied uniformly to all of its sources. Two sources can never disagree on where a day boundary falls, because they are given the same zone.

DataRecs resolves that timezone with a strict chain, and never guesses:

1. Job-level timezone — if the job (or the specific temporal dimension) declares a timezone, it is used.
2. Workspace default timezone — otherwise the workspace’s configured default is used.
3. Hard error — if neither is set, the run is rejected with a validation error (HTTP 400) at trigger time. DataRecs does not silently fall back to UTC.

Caution

There is no implicit UTC. A temporal reconciliation with no resolvable timezone is a configuration error, surfaced immediately — never a run that quietly buckets in the “wrong” zone and produces a plausible-looking but incorrect result. Set a timezone on the job, or a default on the workspace.

Timezones are validated against the IANA timezone database. An unrecognised name (a typo like Europe/Londn, or a non-IANA alias) is rejected at job save time, not at run time.

Where a connection’s source_timezone fits in

A connection can declare a source_timezone. This is not the bucketing zone. It has a single, narrower job: to interpret a source’s naive timestamps — values stored without any zone offset (a PostgreSQL timestamp, a MySQL datetime, a SQL Server datetime2, and so on). DataRecs reads those wall-clock values as the declared source_timezone to pin them to an absolute instant, then buckets that instant in the reconciliation’s resolved bucketing timezone.

Caution

source_timezone is not yet a self-serve field on datarecs_connection/POST /connections — see the availability note at the top of this page. Sources with zone-aware timestamp columns (PostgreSQL timestamptz, BigQuery TIMESTAMP, an ISO string with an offset) are unaffected, since source_timezone is never consulted for them.

Timestamps that already carry a zone (a PostgreSQL timestamptz, a BigQuery TIMESTAMP, an ISO string with an offset) are absolute already — source_timezone is not consulted for them.

Note

Practical consequence: a zone-aware source and a naive source that hold the same logical data (the naive one declaring the correct source_timezone) select the identical rows for a window and land in the identical buckets. The naive column is interpreted, not guessed.

Time windows are half-open

Every time window in DataRecs is half-open: it includes the start instant and excludes the end instant.

start <= row_time  AND  row_time < end

A row whose timestamp is exactly the window’s end is not included — it belongs to the next window. This is the same rule across every source engine, and it is identical whether the column is zone-aware or naive.

Half-open boundaries are what make adjacent windows tile perfectly: [00:00, 01:00) and [01:00, 02:00) cover every instant exactly once, with no gap and no double-count at 01:00.

Daylight-saving transitions

Bucketing is correct across DST in both directions, because a bucket is defined by local calendar boundaries in the bucketing timezone — not by a fixed 24-hour offset arithmetic.

• Spring forward — in Europe/London, 29 March 2026 is a 23-hour day (clocks jump 01:00 → 02:00). Bucketing by day still produces exactly one bucket for 29 March, containing every instant that is calendar-29-March in London.
• Fall back — 25 October 2026 is a 25-hour day (clocks repeat 01:00 → 02:00). It likewise produces exactly one 25-hour day bucket; the repeated local hour is not split or lost.

Across a transition, a zone-aware source and a naive source with the same logical data select the identical rows and produce the identical bucket keys. Hour-granularity bucketing is DST-correct too, including at the transition hour.

How bucketing is computed per source

DataRecs computes bucket keys at the source wherever the source’s engine supports timezone-aware truncation natively — the grouping happens inside the query the source runs, so raw rows are never pulled back just to be re-bucketed.

• Engine-native — PostgreSQL, MySQL, SQL Server, Oracle, BigQuery, and the in-worker engine used for spreadsheets compute buckets using the engine’s own timezone-aware date functions.
• Offset strategy (Db2) — Db2 lacks reliable named-timezone support, so DataRecs supplies the bucketing zone’s UTC-offset transitions to the query itself and computes the bucket by applying the correct offset per instant. The result is equivalent to the engine-native path — verified byte-for-byte against an independent reference in the conformance suite.

To keep every engine agreeing on where a boundary falls, the IANA timezone database is pinned to one version and used consistently across the platform (the in-worker engine, the offset generator, and validation). Two sources bucketing in the same zone therefore use the same timezone rules by construction. This design is common to every engine listed; see the availability note at the top of this page for which engines it’s live on today.

Spreadsheet sources

Google Sheets and Excel/SharePoint have no database engine to push a GROUP BY into, so DataRecs buckets their rows in-worker (using its embedded analytical engine) with the same half-open windows, the same resolved bucketing timezone, and the same DST semantics as every database source. Spreadsheet time data is not a second-class citizen.

The transparency record — “why did my buckets shift?”

Every run that uses temporal dimensions records, per dimension, exactly how time was handled:

Field	Meaning
timezone	The resolved bucketing timezone (e.g. Europe/London).
source_of_timezone	Where it came from — job or workspace.
granularity	hour, day, week, or month.
as	Whether buckets are compared as calendar date or as an absolute timestamp.
strategy	How the bucket was computed — engine-native or the offset strategy.

This record is exposed on the run-detail API, so when a set of buckets looks different from a previous run you can see precisely which timezone (and where it was inherited from) each dimension used. It is the authoritative answer to “why did my buckets shift” — no guessing required.

Note

The transparency record is available via the API today. A dedicated surfacing of it in the console results view is planned as a follow-up.

Conformance and testing

Temporal correctness is proven, not asserted, at the algorithm level: an independent reference implementation (built directly from the pinned IANA rules) computes the expected window membership and bucket keys, and the design is checked against it — including the spring-forward 23-hour day, the fall-back 25-hour day, naive columns with a non-UTC source_timezone, date-only columns, and the Db2 offset-vs-engine-native equivalence. This hermetic conformance suite runs continuously.

Tip

A separate live matrix additionally verifies each engine end-to-end against a real database of that type. This live per-engine verification is what gates an engine’s rollout — see the availability note at the top of this page. The hermetic suite proves the algorithm is correct; the live matrix proves a given engine’s actual driver, extraction, and API path deliver that same correctness in production.

Summary

• A reconciliation buckets in one timezone, applied to every source, resolved job → workspace → error. There is no silent UTC fallback.
• A connection’s source_timezone interprets that source’s naive timestamps; it is never the bucketing zone. It is not yet a self-serve API field (see the availability note above).
• Windows are half-open (>= start AND < end) by design, on every engine as it rolls out.
• Bucketing is DST-correct in both directions by design — a 23-hour spring-forward day and a 25-hour fall-back day each bucket as exactly one day.
• A zone-aware source and a correctly-declared naive source are designed to select the same rows and produce the same buckets across every boundary, including DST transitions, on every engine as it rolls out.
• Every temporal run records its resolved timezone, source, granularity, and strategy, exposed on the run-detail API.
• Today, this is live on PostgreSQL. Other engines are rolling out — see the availability note at the top of this page and the changelog.