Time zones in pandas: the silent bug

[Fairman]

Time zones in pandas: the silent bug

Python's pandas library is a favorite for tick analysis, and its time zone handling has several traps.

Timestamps without a time zone are "naive", and arithmetic between naive and aware timestamps either fails or silently assumes the wrong zone. Data loaded from a CSV is naive by default. Converting it to UTC without first declaring its original zone can shift everything by hours.

Daylight saving transitions create ambiguous or nonexistent times. In autumn, one hour occurs twice. In spring, one hour never occurs. Operations on local-time data can duplicate or drop rows around these dates.

Grouping by day depends on the zone. A daily summary in UTC splits sessions differently from one in New York time.

The safest approach is to convert everything to UTC at the moment of loading, keep it in UTC throughout, and convert to a local zone only for display.

A test: check the number of rows per day around the DST change dates. If a day has 23 or 25 hours' worth of data in a UTC series, something went wrong.

These issues rarely trigger errors. They quietly distort results until someone notices that the London open looks an hour early.

Stored tick data: which time zone convention does the setup use?

[Amanhasnoname]

This one bit me properly when I first moved my tick data into pandas. I had a CSV exported from my broker's server, which runs on GMT+2 with daylight saving, and I treated it as UTC because the timestamps looked clean. Every session statistic I calculated was off by two or three hours depending on the time of year, and I only noticed when my London open volatility appeared to peak at a strange time.

My fix was exactly what you suggest. On load I declare the source zone with tz_localize, convert straight to UTC, and never touch local time again until I'm plotting something. I also keep a small check that counts rows per hour across the March and October change weeks, because that's where the duplicates showed up for me.

For storage, everything goes into parquet files in UTC with the original broker offset noted in the filename. It feels fussy, but it's saved me from redoing a backtest more than once.