Sam Austinon November 2, 202613 min read

Time Zones, DST, and Messy Timestamps: Fix Your Time Data Before Modeling

Time Zones, DST, and Messy Timestamps: Fix Your Time Data Before Modeling
Contents

Figure 1: Time Zones, DST, and Messy Timestamps: Fix Your Time Data Before Modeling — your model cannot tell you that 02:00 on a spring Sunday never happened

Your model cannot tell you that 02:00 on a spring Sunday never happened. It will quietly treat it as one more row, and your forecast will pay for it later.

I tested the usual timestamp traps in pandas 3.0.6 on synthetic data. Several of my own assumptions turned out wrong, and I will point those out as we go.

The Golden Rule: Store UTC, Display Local

UTC has no daylight saving, no skipped hours and no repeated hours. Local time has all three. So keep your raw timestamps in UTC, and convert to local time only when you need a local-clock feature [3]:

ts = pd.to_datetime(df["ts"], utc=True)      # parse once, in UTC
local = ts.dt.tz_convert("America/New_York")  # derive local features from this

The docs say utc=True always returns timezone-aware UTC: naive inputs are localized as UTC, and aware inputs are converted to UTC [1]. Note that distinction. Naive inputs get labelled as UTC, not converted, so if they were really local times, you have just attached the wrong label.

Trap 1: Local Times That Do Not Exist

On 2024-03-10 in New York, clocks jump from 01:59 to 03:00 [5]. A logger that writes naive local timestamps may contain 02:00 anyway.

I tried localizing a naive range across that night. The default raised a ValueError naming 2024-03-10 02:00:00 as nonexistent. The documented options are [2]:

  • nonexistent="shift_forward": moves 02:00 to 03:00. My run did exactly that, and then two rows carried the label 03:00.
  • nonexistent="NaT": returned one NaT row.

Neither option is "correct." Shifting keeps the row and creates a duplicate label. NaT drops the information. Decide per dataset, and write the decision down.

Trap 2: Local Times That Happen Twice

On 2024-11-03, 01:00 happens twice. Localizing raised an error by default. The ambiguous options behaved like this in my test:

  • "infer" worked on sequential data where the hour was written twice. It assigned -04:00 to the first copy and -05:00 to the second.
  • "NaT" on a series with only one 01:00 row returned one NaT, because it cannot tell which hour you meant.
  • "infer" on that same single row raised instead, with "as there are no repeated times."

There is also a boolean array option that lets you mark which rows are daylight time, which the docs describe [2].

The best fix is upstream: make the source system log UTC or include offsets. Repairing ambiguity after the fact is guesswork with a good API.

Trap 3: Days That Are Not 24 Hours

I converted a year of hourly UTC data to New York time and counted hours per local day, dropping the partial first and last days that my UTC range edges created. My first run forgot, and showed 5-hour and 19-hour days.

  • 363 days had 24 hours.
  • One day (2024-03-10) had 23.
  • One day (2024-11-03) had 25.

A "daily total" or a "daily average times 24" changes meaning on those two days. If you aggregate by local day, check those two dates.

I also stripped the zone from the local series to mimic a naive log. The steps between rows were 8,781 of one hour, one of two hours (the skipped hour) and one of zero (the repeated hour). My guess going in was that I would see a negative step. I did not. Anything that uses diff, shift or rolling windows on naive local time inherits both oddities.

Trap 4: The Wrong Hour-of-Day Feature

Does it matter whether your hour feature comes from UTC or from the local clock? I generated hourly demand driven by the local clock, with peaks at 08:00 and 19:00 local, for three years. Then I trained gradient boosting with forward cross-validation, five folds, and a noise floor of 2.39:

Hour feature MAE
Hour in UTC 3.25
Hour at a fixed UTC-5 offset 3.25
Hour after tz_convert to local 2.39

The local-hour model reached the noise floor. The UTC-hour model sat 36% above it.

The fixed-offset version scored the same as plain UTC. Why? Relabelling every hour by a constant does not change what a tree can learn. The pattern still shifts by an hour twice a year, so the model has to learn two versions. Only a real timezone conversion removes the shift [4].

Fair caveat: I built this series so the clock drives demand. If your signal follows machines or global users, UTC may be right.

Trap 5: Mixed Offsets

Two strings that mean "noon in New York" carry different offsets, -05:00 in winter and -04:00 in summer. In pandas 3.0.6, to_datetime on that mix raised a ValueError: "Mixed timezones detected. Pass utc=True in to_datetime or tz='UTC' in DatetimeIndex to convert to a common timezone." Passing utc=True produced 17:00 and 16:00 UTC, and converting back gave 12:00 local for both [1].

I only tested this version. The docs mention limitations for mixed offsets, and I would not assume older versions behave the same way.

Trap 6: Epoch Numbers in the Wrong Unit

A column of values like 1704067200000 is 2024-01-01, if you know it is in milliseconds. Without unit, pandas assumes nanoseconds:

  • Default: both values landed on 1970-01-01, silently, with no error.
  • unit="ms": gave 2024-01-01 and 2024-01-02.
  • Seconds read as milliseconds also landed in 1970.

After any numeric parse, check the minimum and maximum dates. A column that starts in 1970 almost always has a unit problem.

Trap 7: Ambiguous Date Strings

Is 03/04/2024 March 4 or April 3? Explicit formats gave different answers: %m/%d/%Y returned March 4 and %d/%m/%Y returned April 3.

Without a format, a column containing 03/04/2024 and 13/04/2024 raised an error, because parsing settled on month-first from the first value and then failed on the 13th [1]. The docs also warn that dayfirst=True "is not strict, but will prefer to parse with day first," so I would pass an explicit format instead.

A Pre-Modeling Checklist

  1. Parse with utc=True, or with explicit offsets, and keep UTC as the stored truth.
  2. Check your source: does it log naive local time? If so, decide how to treat the skipped and repeated hours.
  3. Derive local-clock features with tz_convert, not with a fixed offset.
  4. Count rows per local day and look at the 23- and 25-hour days.
  5. Check date ranges after parsing numbers, and pass unit.
  6. Use explicit formats for date strings.
  7. Test on the transition weekends, both of them, before trusting a pipeline.

Common Pitfalls

  1. Treating utc=True as a conversion. It relabels naive input as UTC; it does not shift it.
  2. Localizing a naive log without a plan for the missing hour. The default raises, and the two fixes both lose something.
  3. Aggregating by local day and assuming 24 rows per day. Two days a year are not.
  4. Building the hour feature from a fixed offset. It scores the same as UTC, because the shift is still there.
  5. Parsing epoch numbers without unit. You get 1970 and no warning.
  6. Letting pandas guess the date format. One unambiguous row decides the convention for the whole column.
  7. Only testing in summer. Every trap here hides until a transition weekend.

Best Book to Read

CoverBookDescriptionGet it
Cover of “Python for Data Analysis” Python for Data Analysisby Wes McKinney the time series chapter is written by the person who built pandas, and it covers time zone handling, DST transitions and resampling with exactly the failure modes this post measures. View on Amazon

As an Amazon Associate I earn from qualifying purchases. Links on this page are affiliate links, and I may earn a commission at no extra cost to you.

Wrapping Up

Most of these bugs fail quietly. A 1970 date, a duplicated 03:00 and a 25-hour day all look like ordinary data until a forecast misses on the wrong Sunday.

Pick your most important time column and run three checks today: the minimum and maximum date, the rows per local day around both DST weekends, and whether your hour-of-day feature comes from tz_convert. Ten minutes now beats an unexplained miss in March.

Frequently Asked Questions

Why should I store timestamps in UTC instead of local time?

Because UTC has no daylight saving transitions: no hour is skipped and no hour happens twice. Local time has all three problems. Keep the stored truth in UTC and derive local-clock features with tz_convert when you need them, so the conversion happens once and stays auditable.

What does pandas do with a time that does not exist because of DST?

tz_localize raises a ValueError by default and names the offending timestamp. You can pass nonexistent="shift_forward" to move it to the next valid time, which creates a duplicate label, or nonexistent="NaT" to drop the row. Neither is correct in general, so pick one per dataset and record the choice.

How do I handle the hour that happens twice when clocks go back?

Pass ambiguous="infer" when your data is a clean sequence that contains both copies of the hour, and pandas will assign the daylight offset to the first and the standard offset to the second. For a single ambiguous row there is nothing to infer from, so ambiguous="NaT" returns NaT. The real fix is upstream: log UTC or log offsets.

Why does my hour-of-day feature not help my forecast?

If the hour came from UTC or from a fixed offset, the local pattern shifts by an hour twice a year and the model has to learn two versions of it. In my test, the local hour reached the noise floor at 2.39 MAE while the UTC hour scored 3.25. Derive the hour with tz_convert, not with a constant offset.

Why did my epoch column parse as 1970?

Because pandas assumes nanoseconds when you pass no unit. A millisecond value like 1704067200000 is 1.7 seconds in nanoseconds, so it lands on 1970-01-01. Pass unit="ms" or unit="s" explicitly, and check the minimum and maximum date after every numeric parse.

Sources

What are You Looking For?

esc