09:30 is not on the hour

The standard way to bucket time-series data into wider bars is one line:

const bucket = Math.floor(ts / width) * width;

Every bar of the same width lands on a stable grid, buckets never overlap, and it costs one division per row. For crypto it is correct. For a US equity session it produces a chart that is subtly, permanently wrong.

What the grid is anchored to

ts / width anchors the grid on the Unix epoch: midnight UTC, 1 January 1970. So hourly buckets begin at every UTC hour, and 4-hour buckets at 00:00, 04:00, 08:00 UTC and so on.

A US trading session opens at 09:30 Eastern. Under EDT that is 13:30 UTC; under EST it is 14:30 UTC. Neither is on the hourly grid, and neither is anywhere near the 4-hour one.

Measured against the real store before the fix, hourly AAPL bars began at 13:00 UTC. There is no market data between 13:00 and 13:30 — the session had not opened. So the first bar of every single trading day contained 09:30 to 10:00 ET: thirty minutes of data wearing an hour's label, handed to the backtest engine as a full bar.

The 4-hour case is worse

On a 4-hour grid the misalignment stops being a runt bar and starts changing the number of bars per day. A 09:30–16:00 session laid over UTC 4-hour boundaries falls into two buckets under EDT and three under EST.

So the same trading day has a different shape in summer and in winter. Not because anything about the market changed, and not because the data changed — the underlying minute bars are byte-for-byte the same rows. A strategy backtested across a daylight-saving boundary silently switches to a different bar structure partway through the sample, and any parameter tuned on one side of the changeover is tuned on the wrong thing on the other.

This is the kind of defect that never announces itself. Nobody stares at a 4-hour chart counting bars per day.

The fix: anchor on the session, not the epoch

Buckets are now folded in JavaScript, anchored on each session's own 09:30 ET open:

const open = sessionOpenForDay(Math.floor(r.ts / 86400), openCache);
const bucket = open + Math.floor((r.ts - open) / width) * width;

Which requires knowing, for each trading day, what 09:30 ET was in UTC — and that answer moves twice a year on dates that themselves have changed by legislation before. Hardcoding the offset is how you write a bug that arrives in March. It comes from ICU instead:

/** Seconds to add to a UTC instant to read it as an ET wall
 *  clock — negative, -4h under EDT and -5h under EST. Derived
 *  from ICU rather than hardcoded because the DST changeover
 *  dates move. */
function etOffsetSeconds(utcSeconds) { /* ... */ }

One subtlety in resolving the offset: you cannot ask for the offset at midnight, because midnight is the boundary you are trying to resolve. The lookup is done at noon UTC — which is 07:00 or 08:00 ET, safely inside the same ET calendar day in either offset — and cached, so the cost is one timezone lookup per trading day rather than one per minute bar.

Why not do it in SQL

The bars live in SQLite, and folding buckets in the query would be the natural place for it. SQLite has no timezone database. It cannot answer “what was the UTC offset for America/New_York on this date”, and any answer written into the SQL by hand is the hardcoded offset again, one refactor away from being wrong for half the year.

The same constraint decided something further upstream. Whether a minute bar falls inside regular trading hours is computed once at import time, in Python, where zoneinfo can resolve it — and stored as a column:

in_session INTEGER NOT NULL

Timezone logic runs where a timezone database exists. Everywhere else reads a precomputed integer.

What it reads now

Verified against the real store rather than a fixture: hourly bars open at 09:30 ET with seven per day. Four-hour bars open at 09:30 and 13:30, two per day in both offsets — the DST-dependent third bucket is gone. The 1-minute interval still returns every regular-hours row it holds.

Two related decisions, since they come from the same place

Every interval is filtered to regular hours, not just the daily one. The store keeps the raw 04:00–19:59 ET rows, so this is a query-layer choice and stays reversible. The reason: serving extended hours at 1h but not at 1d would make the same trading day price differently depending on which chart you looked at, and a 04:07 ET print is not something a strategy could realistically have filled against.

Empty buckets are omitted, never forward-filled. A thinly traded minute with no prints is not a minute where the price stayed flat — it is a minute with no information. Forward-filling invents a bar that never existed and hands it to a backtest as tradeable.

* Resample 1m rows up to `interval` ... Buckets with no
* underlying rows are omitted — never forward-filled, because
* a missing minute means no trade occurred.

The general point

Financial time series are not evenly spaced samples of a continuous signal, however much they resemble one. They are records of a market that opens at a local wall-clock time, in a timezone that observes daylight saving, on days chosen by a holiday calendar. Any resampling that treats them as evenly spaced will be right most of the time and wrong in a way nobody looks for.

It is the same category as the scan window that read a quarter of the data: an assumption about market continuity, silently false for equities, producing output that looks entirely normal. Both were found by measuring the real store instead of reading the code, which is becoming the only method we trust for this layer.