Timestamp Alignment and Time Zones

Two files. Both have a column called timestamp. Both look sane when you print the head. Merging them is one line of code, and that line is where a great many cross-market backtests get their entire performance.

The problem is that a wall-clock time is meaningless without a place attached to it, and most systems throw the place away. "16:00" is a fact about a room in Manhattan, not about the universe. Once two feeds recorded in different rooms sit in the same dataframe, the join is silently comparing different moments.

The map you should have on your wall

Venue	Local close	UTC in January	UTC in July
Tokyo (JPX)	15:00 JST	06:00	06:00 — no DST
Mumbai (NSE)	15:30 IST	10:00	10:00 — no DST
Frankfurt (Xetra)	17:30 CET / CEST	16:30	15:30
London (LSE)	16:30 GMT / BST	16:30	15:30
New York (NYSE)	16:00 EST / EDT	21:00	20:00

Two things to notice. Asia does not observe daylight saving at all, so the Tokyo–New York gap changes twice a year even though nothing in Tokyo changes. And the US and Europe do observe it, on different dates: the US springs forward on the second Sunday in March and falls back on the first Sunday in November; the EU and UK switch on the last Sundays of March and October.

So for roughly three weeks in March and one week around the start of November, London is four hours ahead of New York rather than the usual five. Four weeks a year, every year, the offset your code hard-coded is wrong by an hour.

Worked example: a lead-lag signal that lived in four weeks

A cross-market signal: European close-to-close returns predict the following US session. Universe of index futures, twelve years of data, one trade a day. The two feeds arrive with naive local timestamps and are joined on a fixed five-hour offset.

Backtest: Sharpe 1.35, 9.4% a year on 7% volatility, profitable in eleven of twelve calendar years. It is a plausible economic story — information flows west — and it survives the usual checks on turnover and costs.

Then someone breaks the P&L down by calendar week. Four weeks a year, the DST-mismatch weeks, are 7.7% of the sample and 52% of the cumulative profit. In those weeks the fixed offset places the "European close" row an hour later than it really was, which lands it after the US open. The signal is reading US prices and predicting US prices.

Remove those four weeks and re-run: Sharpe 0.4, about 2.1% a year on the same volatility. After costs it is roughly flat.

Verdict: there is a real lead-lag effect here, and it is far too small to trade alone. The tradeable-looking version was an hour of the future, delivered four weeks a year by a hard-coded number. The diagnostic that caught it — profit concentration by calendar period — costs one line and should be run on everything.

The other three failures on the same column

Provenance. A vendor's timestamp is often the moment they processed the message, not the moment the exchange matched it. On a slow feed that difference runs to hundreds of milliseconds and drifts with load, which is fatal for anything intraday. See Timestamp Provenance: Exchange Versus Vendor.

Units. Epoch timestamps appear in seconds, milliseconds, microseconds and nanoseconds. Milliseconds parsed as seconds land in the year 54,000; seconds parsed as milliseconds land in January 1970. Those are obvious. The dangerous case is microseconds parsed as milliseconds, which yields a plausible-looking date a few decades off — recent enough to survive a range check.

Bar edges. A bar labelled 09:31:00 may cover 09:31–09:32 or 09:30–09:31 depending on the vendor. That is a one-bar look-ahead, worked through in Decision Time Versus Data Timestamp.