Cleaning Market Data: Timestamps, Sessions and DST

Part 3 of 20 · How to Algo · Previous: Bar data vs tick data · Next: Continuous futures and rolls

Bad market data rarely looks bad. It looks like a normal table with plausible integers in it. A timestamp in seconds where microseconds were expected, a session gate an hour late in winter, a side flag that means “quote” instead of “trade”: none of these throw an error. They produce a backtest, and the backtest produces a number.

Every example below happened in our own data or code. None was caught by the code that produced it; each was caught by a check that compared the data with something it should agree with.

Rule one: never compute session times as tsSec % 86400

The single most common session bug we have seen is gating on UTC time of day. It looks harmless:

mins = (ts_sec % 86400) // 60          # minutes since UTC midnight
in_session = 13*60 + 30 <= mins < 20*60

In summer, 13:30 UTC is 09:30 in New York. In winter it is 08:30. The gate is correct for roughly eight months of the year and an hour early for the rest. New York left daylight saving time on 1 November 2026, so from this week the cash session, 09:30 to 16:00 New York, runs 14:30 to 21:00 UTC instead of 13:30 to 20:00.

We audited our own live agents for this in July 2026. Six of them had session logic hard-coded in UTC. A gate written as 13:00 to 21:00 UTC is 09:00 to 17:00 New York in summer and 08:00 to 16:00 in winter. One of them would have traded ninety minutes of pre-market as soon as the clocks changed. Two daily agents took their “closing” reference at 20:00 UTC, which is 16:00 New York in summer and 15:00 in winter, so half the year they would have measured the close an hour before the close.

The fix is boring: convert every timestamp to exchange-local time with a real time-zone library, and gate on that. For CME equity index futures that means New York for the cash session and Chicago for the exchange’s own schedule. Then test with one summer and one winter day, explicitly.

The same bug has a cousin in replay code. Several of our older agents keyed their trading day on ts_sec // 86400, which starts the day at UTC midnight: 20:00 New York in summer. Replaying those agents day by day cuts every overnight range in half. Futures trading days run from the Globex reopen at 18:00 New York to 17:00 the next day (our Asia and London audit rebuilt all 23 hours of them), and a replay has to stream continuously across that boundary.

The clock label is not the clock

In July 2026 we published an audit of a well-known opening-range strategy and reported that its claimed $151k came out at $42k on our data. The error was ours. The report’s input panel said ORB_Start_H_NY 8, ORB_Start_M_NY 30. We read that as 08:30 New York. It was 08:30 Chicago, which is 09:30 New York, the cash open.

Same rules, same costs, same ticks: under the literal New York reading the strategy made +$18,969 at t = 0.49; under the Chicago reading, +$121,088 at t = 2.39. One hour of clock carried the whole result. The corrected audit is still online with the correction at the top. The report even contained a clue (its prose said positions flattened at 15:00 ET while its panel said 14) and we treated it as a contradiction instead of an offset.

The lesson: when a strategy’s specification includes times, find one fact in the document that pins the time zone, such as “the cash open”, and check your reading against it before testing anything.

Define the window, then check you measured that window

A related mistake: measuring the right effect over the wrong window. On ES we tested the drift underneath a popular “Sunday open” mechanic. Our first pass held the Sunday evening session, 18:00 to 24:00 New York, and found +$15.84 a night at t = 0.49. The Nasdaq study we were comparing against had held to Monday’s cash open. Run on that window, the ES drift was +$163.62 a Sunday at t = 3.28. Same market, same nights, and a tenfold difference because a session boundary was defined differently in two places. The Sunday open test documents both.

Write session windows as named constants in exchange time, with the reason for each boundary next to them.

Timestamp units

Our tick store uses microseconds since the epoch. Twice, both times in June 2026, a recorder wrote seconds into that column. Nothing failed: the values were valid integers, they just pointed to January 1970. Both stretches were found months later by accident.

The two cases needed opposite treatment, and the decision was checkable rather than a matter of taste:

• NQ: 124,634 broken rows, of which 99.9% were exact duplicates of correctly stamped data. Deleted. Repairing them would have counted each of those trades twice.
• ES: 910,329 broken rows, and only one correctly stamped ES row existed in the whole window. Repaired by multiplying by one million. Deleting them would have erased a full trading day.

Even repaired, that ES day is damaged forever: 910,226 trades share 52,202 timestamps, all on whole seconds. The order of trades inside each second is gone, so any microstructure, delta or footprint analysis over that day is meaningless, and nothing in the file says so. We now keep a list of such days and exclude them from anything sub-second.

The research-side version of the same bug: computing epoch minutes as ts.astype("int64") // 10**9 // 60 assumes nanoseconds. After a Parquet round trip the column was microseconds, so the result was off by a factor of 1,000, and in one gold study it silently scrambled the minute index of an entire overnight session, which then looked like a dead market. Use explicit time arithmetic ((ts - epoch) // pd.Timedelta(minutes=1)), not unit assumptions.

Checks that would have caught both: every night, verify that all timestamps of the previous day decode to dates between 2000 and 2100, and track the share of distinct timestamps per symbol and day. A sudden drop in distinct timestamps means lost resolution.

The side flag: trade, quote, or noise?

The aggressor side (whether a trade was a market buy or a market sell) is the input for every order-flow study. It is also the field most likely to be wrong, and we have found three different failures.

Quotes labelled as trades. A historical NQ source we bought had a type == 1 record with side 0 or 1, which we read as sell and buy trades. They were best-bid and best-ask quote updates. The real trades were side == 2, and the vendor provided no aggressor at all. The tell was volume: the “trades” added up to roughly 25 times NQ’s real daily volume. Rebuilt with the side inferred from the prevailing bid and ask, one-minute delta correlated +0.72 with the bar’s price change.

An inverted replay. A tool that refilled gaps from the exchange’s history service had its buy and sell mapping reversed. Every refilled stretch was mirrored. We flipped 2,485 files, 15.7 million prints, after the check below exposed it.

A meaningless side. One refill source turned out to be an aggregated relay recording: whole-second timestamps, about 15 lots per print, and a side that agreed with the tick rule 50% of the time, which is chance.

The check that separates all three takes seconds: compare the recorded side with the tick rule (a trade above the previous price is probably a buy). On our clean data agreement is around 96% on ES and 88% on NQ. The inverted replay scored 2–8% on ES. The relay scored 50%. A second check: correlate per-minute delta with the minute’s price change. It should be clearly positive.

Duplicates, glued lines and bad ticks

Vendor files fail in mundane ways, which is why our historical tick data guide lists what to check before you buy. One monthly archive we import contained daily files that ended without a newline, so the last line of one day was glued onto the first line of the next. Four rows in one month were corrupted that way, enough to produce a single absurd price. Our importer now counts the columns of every row and refuses to upload a month with even one malformed line.

Weekend and holiday files are another source. We keep corrupted weekend partitions in quarantine rather than letting them into the store, and we drop depth partitions where the book sits frozen at one price for 24 hours.

For price outliers, compare each trade with a rolling median of recent trades and flag anything more than a set number of ticks away, then look at the flagged rows before deleting them. A real fast market and a bad print look the same to a filter.

Missing days are not always gaps

Keep an exchange calendar and compare every missing or short session against it. When we audited our store for holes, 9 January 2025 showed up as an empty day. It was not a gap: the exchange was closed. The opposite also happens: a day that should be full and contains half a session is a recording failure, and it should be refilled or excluded rather than silently treated as a quiet day.

After every restart of our live recorder we now run an empty-minute scan: one query that lists every minute of the regular session with zero prints, per symbol. It is the cheapest gap detector we have.

Checklist

1. Store all timestamps in one unit, documented, and check every day that they decode to sane dates.
2. Track distinct timestamps per symbol and day; a drop means lost resolution.
3. Gate sessions in exchange-local time via a time-zone library, never UTC arithmetic.
4. Test session logic on one summer and one winter date.
5. Pin the time zone of any third-party specification against a known event such as the cash open.
6. Check aggressor side against the tick rule and delta against price change.
7. Compare daily volume with the exchange’s published volume.
8. Count columns on import; reject files with malformed rows.
9. Compare missing days with an exchange calendar before calling them gaps.
10. Before repairing or deleting broken rows, check whether a clean copy exists.

Clean ticks are still not a continuous price history for futures, because each contract expires. Stitching contracts together introduces its own set of artefacts. Part 4: Continuous futures contracts, roll methods and backtesting.