My Time Column Sorts or Joins Wrongly
On this page
Problem statement
A time column that looks correct in every row can still be wrong as a sequence. The symptoms are specific and easy to misread:
- track speeds come out negative
- an hour of data appears twice, or vanishes, once a year
- a temporal join returns nothing, or returns everything
- sorting by time interleaves two journeys that were hours apart
All four come from the same root: a timestamp is only meaningful with a timezone, and most formats, most exporters and most read_csv calls drop it. The examples below use real hourly weather for Oakham across the October 2025 clock change, from Open-Meteo's archive, requested once in UTC and once in "Europe/London".
Quick answer
Parse to UTC at the boundary, and never store a naive timestamp:
# from ISO 8601 strings with offsets, or Z, or a mixture
mixed = pd.Series(["2025-10-26T00:30:00+01:00", "2025-10-26T01:30:00+00:00", "2025-10-26T02:30:00Z"])
print(pd.to_datetime(mixed, utc=True, format="ISO8601").tolist())
# from genuinely local times whose zone you know
local = pd.Series(pd.to_datetime(["2025-10-26 00:30", "2025-10-26 01:30", "2025-10-26 01:30", "2025-10-26 02:30"]))
print(local.dt.tz_localize("Europe/London", ambiguous=[True, True, False, False]).dt.tz_convert("UTC").tolist())[Timestamp('2025-10-25 23:30:00+0000', tz='UTC'), Timestamp('2025-10-26 01:30:00+0000', tz='UTC'), Timestamp('2025-10-26 02:30:00+0000', tz='UTC')]
[Timestamp('2025-10-25 23:30:00+0000', tz='UTC'), Timestamp('2025-10-26 00:30:00+0000', tz='UTC'), Timestamp('2025-10-26 01:30:00+0000', tz='UTC'), Timestamp('2025-10-26 02:30:00+0000', tz='UTC')]
The two 01:30s are one hour apart in reality — one in British Summer Time, one in GMT — and only the ambiguous argument says which is which. pandas refuses to guess by default: tz_localize raises on the hour that occurs twice each autumn and on the hour that does not exist each spring, unless you tell it how to resolve them. Keep that default.
Find what you have, parse as UTC, localise strictly
1. Find out what you actually have
for name, frame in (("requested in UTC", utc_frame), ("requested in Europe/London", local_frame)):
print(f"{name:27} {frame['time'].dtype} first {frame['time'].iloc[0]}")
print("parsed with utc=True:", pd.to_datetime(utc_frame["time"], utc=True).dtype)requested in UTC datetime64[us, UTC] first 2025-10-25 00:00:00+00:00
requested in Europe/London str first 2025-10-25T00:00
parsed with utc=True: datetime64[us, UTC]
objectorstr— still strings. Nothing has been parsed.datetime64[ns]— parsed and naive. No zone; the values are ambiguous.datetime64[ns, UTC]— parsed and aware. Safe.
A naive column is not a bug in itself, but it is an unlabelled unit. It becomes a bug the moment two sources with different zones meet.
2. Parse with utc=True at the point of reading
csv = "time,value\n2025-10-26T00:30:00+01:00,1\n2025-10-26T01:30:00+00:00,2\n"
print("parse_dates, two offsets:", pd.read_csv(io.StringIO(csv), parse_dates=["time"])["time"].dtype)
one_offset = "time,value\n2025-10-26T00:30:00+01:00,1\n2025-10-27T00:30:00+01:00,2\n"
print("parse_dates, one offset: ", pd.read_csv(io.StringIO(one_offset), parse_dates=["time"])["time"].dtype)
print("to_datetime(utc=True): ", pd.to_datetime(pd.read_csv(io.StringIO(csv))["time"], utc=True).dtype)parse_dates, two offsets: str
parse_dates, one offset: datetime64[us, UTC+01:00]
to_datetime(utc=True): datetime64[us, UTC]
With two offsets in the column, parse_dates left it as strings, without a warning. With one, it produced a fixed UTC+01:00 offset — which is not a timezone: it knows nothing about the clocks changing. utc=True puts every row on one scale. If the strings carry Z, it recognises it; if they carry nothing, it assumes UTC — which is a guess, and one you should make deliberately rather than by omission.
3. Localise genuinely local times, and let it raise
Naive local times need tz_localize. Two dates a year have no unambiguous answer:
- Autumn, when clocks go back: 01:30 local occurs twice.
ambiguous="raise"refuses;ambiguous=Truepicks the first (DST) occurrence. - Spring, when clocks go forward: 01:30 local never happens.
nonexistent="raise"refuses;nonexistent="shift_forward"moves it.
for label, stamps in (("autumn", ["2025-10-26 00:30", "2025-10-26 01:30", "2025-10-26 01:30", "2025-10-26 02:30"]),
("spring", ["2025-03-30 00:30", "2025-03-30 01:30", "2025-03-30 02:30"])):
try:
pd.Series(pd.to_datetime(stamps)).dt.tz_localize("Europe/London")
except ValueError as exc:
print(f"{label}: {exc}")
spring = pd.Series(pd.to_datetime(["2025-03-30 00:30", "2025-03-30 01:30", "2025-03-30 02:30"]))
print("shift_forward:", spring.dt.tz_localize("Europe/London", nonexistent="shift_forward").dt.strftime("%H:%M %Z").tolist())autumn: Cannot infer dst time from 2025-10-26 01:30:00, try using the 'ambiguous' argument
spring: 2025-03-30 01:30:00 is a nonexistent time due to daylight savings time. Try using the 'nonexistent' argument.
shift_forward: ['00:30 GMT', '02:00 BST', '02:30 BST']
The default turns a silent corruption into a loud failure on exactly two days a year, which is the correct trade.
4. Check monotonicity per group after parsing
A logger that records wall-clock time is the usual source. Rebuild one from the UTC series — convert to London time, then drop the zone, which is what a device writing local time to a CSV does — and look at the steps:
logger = pd.DataFrame({"station": "Oakham",
"time": utc_frame["time"].dt.tz_convert("Europe/London").dt.tz_localize(None),
"temperature": utc_frame["temperature_2m"]})
print(logger.iloc[24:28].to_string(index=False))
def check_time_order(df, id_col="station"):
for key, sub in df.groupby(id_col):
dt = sub["time"].diff().dt.total_seconds()
print(f" {key}: {int((dt < 0).sum())} backwards steps, {int((dt == 0).sum())} repeated times, "
f"steps seen: {sorted(set(dt.dropna().astype(int)))}")
check_time_order(logger)station time temperature
Oakham 2025-10-26 01:00:00 4.2
Oakham 2025-10-26 01:00:00 3.8
Oakham 2025-10-26 02:00:00 3.8
Oakham 2025-10-26 03:00:00 3.7
Oakham: 0 backwards steps, 1 repeated times, steps seen: [0, 3600]
Hourly data shows the autumn change as a repeated hour — a zero step. Data logged every 15 minutes shows it as a jump back, and a backwards step of exactly −3,600 s is a daylight-saving artefact. A random negative step is unsorted data. A negative step of hours is two sources with different zones concatenated.
5. Store UTC, display local
Keep every stored timestamp in UTC. Convert to local only for display and only at the last moment:
display = utc_frame.assign(local=utc_frame["time"].dt.tz_convert("Europe/London"))
print(display[["time", "local"]].iloc[24:28].to_string(index=False)) time local
2025-10-26 00:00:00+00:00 2025-10-26 01:00:00+01:00
2025-10-26 01:00:00+00:00 2025-10-26 01:00:00+00:00
2025-10-26 02:00:00+00:00 2025-10-26 02:00:00+00:00
2025-10-26 03:00:00+00:00 2025-10-26 03:00:00+00:00
Local time is a presentation format. Storing it makes every downstream join a guess.
Oakham temperatures across the October clock change
The data used on this page
Hourly 2 m air temperature for Oakham (52.67° N, 0.73° W) from 25 to 27 October 2025, across the change from British Summer Time to GMT at 02:00 on 26 October, from the Open-Meteo historical weather API (CC BY 4.0), requested twice: with timezone=GMT and with timezone=Europe/London.
import io
import json
from pathlib import Path
import numpy as np
import pandas as pd
import requests
frames = {}
for label, zone in (("utc", "GMT"), ("local", "Europe/London")):
path = Path(f"openmeteo_{label}.json")
if not path.exists():
path.write_text(requests.get("https://archive-api.open-meteo.com/v1/archive", params={
"latitude": 52.67, "longitude": -0.73, "start_date": "2025-10-25", "end_date": "2025-10-27",
"hourly": "temperature_2m", "timezone": zone}, timeout=60).text)
reply = json.loads(path.read_text())
frames[label] = pd.DataFrame(reply["hourly"])
print(f"{zone:14} {len(frames[label])} hours, utc_offset_seconds={reply['utc_offset_seconds']}, "
f"first {reply['hourly']['time'][0]}")
utc_frame = frames["utc"].assign(time=lambda d: pd.to_datetime(d["time"]).dt.tz_localize("UTC"))
local_frame = frames["local"]GMT 72 hours, utc_offset_seconds=0, first 2025-10-25T00:00
Europe/London 72 hours, utc_offset_seconds=3600, first 2025-10-25T00:00
Example 1 — a parser that refuses to guess
import pandas as pd
def parse_time(series, assume_tz=None, source_name="time"):
"""Parse to UTC, raising rather than guessing on ambiguity."""
parsed = pd.to_datetime(series, errors="coerce", format="ISO8601")
failed = int(parsed.isna().sum() - pd.isna(series).sum())
if failed:
print(f" {source_name}: {failed:,} values failed to parse")
if parsed.dt.tz is None:
if assume_tz is None:
raise ValueError(
f"{source_name} is timezone-naive and no zone was given. "
"Pass assume_tz='UTC' if that is what it is, or the IANA "
"zone the data was recorded in."
)
parsed = parsed.dt.tz_localize(assume_tz, ambiguous="raise",
nonexistent="raise")
print(f" {source_name}: localised to {assume_tz}")
return parsed.dt.tz_convert("UTC")
try:
parse_time(local_frame["time"], source_name="open-meteo local")
except ValueError as exc:
print(" ", str(exc)[:90], "…")
parsed = parse_time(local_frame["time"], assume_tz="Etc/GMT-1", source_name="open-meteo local")
check = pd.DataFrame({"time": parsed, "local_temp": local_frame["temperature_2m"]}).merge(utc_frame, on="time")
print(f" first: {parsed.iloc[0]} | {len(check)} hours overlap the UTC request, "
f"values agree in {(check['local_temp'] == check['temperature_2m']).mean():.0%}")
try:
parse_time(local_frame["time"], assume_tz="Europe/London", source_name="as London")
except ValueError as exc:
print(" declared as Europe/London instead:", exc) open-meteo local is timezone-naive and no zone was given. Pass assume_tz='UTC' if that is …
open-meteo local: localised to Etc/GMT-1
first: 2025-10-24 23:00:00+00:00 | 71 hours overlap the UTC request, values agree in 100%
declared as Europe/London instead: Cannot infer dst time from 2025-10-26 01:00:00, try using the 'ambiguous' argument
The zone passed here is the one Open-Meteo actually used: a single offset of one hour (Etc/GMT-1, whose sign is reversed by the POSIX convention) for the whole three days, taken from the start of the window — so its "local" labels after the clocks went back are an hour ahead of real London time. With that declared, every overlapping hour agrees with the UTC request. Declaring Europe/London, which is what the request asked for, is refused: 01:00 on 26 October is ambiguous in London, and the labels give no way to say which of the two it is — because they were never London time.
Raising on a naive column with no declared zone is the whole point. It forces the decision to be made once, visibly, rather than a hundred times implicitly.
Example 2 — diagnosing a time column
import numpy as np
import pandas as pd
def time_report(df, time_col="time", id_col=None):
"""Everything that can be wrong with a time column, checked."""
s = df[time_col]
print(f" dtype {s.dtype}")
print(f" timezone {getattr(s.dt, 'tz', None)}")
print(f" range {s.min()} .. {s.max()}")
print(f" nulls {int(s.isna().sum()):,}")
print(f" duplicated values {int(s.duplicated().sum()):,}")
groups = df.groupby(id_col) if id_col else [(None, df)]
total_back = 0
for key, sub in groups:
dt = sub.sort_index()[time_col].diff().dt.total_seconds()
back = int((dt < 0).sum())
total_back += back
if back:
worst = dt.min()
hint = ("looks like a DST transition" if abs(worst + 3600) < 2
else "looks like unsorted or mixed-zone data")
print(f" {key}: {back} backwards steps, worst {worst:.0f}s "
f"({hint})")
if not total_back:
print(" time is monotonic within every group")
return total_back
time_report(logger, id_col="station") dtype datetime64[us]
timezone None
range 2025-10-25 01:00:00 .. 2025-10-27 23:00:00
nulls 0
duplicated values 1
time is monotonic within every group
The -3600 test is worth having. A backwards step of exactly one hour is diagnostic, and it points at a different fix from a general sorting problem.
Example 3 — a temporal join that does not silently miss
import pandas as pd
def temporal_join(left, right, on_time="time", tolerance="5min",
by=None):
"""As-of join with both sides checked for zone compatibility."""
for name, frame in (("left", left), ("right", right)):
tz = getattr(frame[on_time].dt, "tz", None)
if tz is None:
raise ValueError(f"{name} side has naive timestamps — "
"localise before joining")
if str(tz) != "UTC":
print(f" converting {name} from {tz} to UTC")
frame[on_time] = frame[on_time].dt.tz_convert("UTC")
left = left.sort_values(on_time)
right = right.sort_values(on_time)
out = pd.merge_asof(left, right, on=on_time, by=by,
tolerance=pd.Timedelta(tolerance),
direction="nearest")
matched = out[right.columns.difference([on_time])[0]].notna().mean()
print(f" {matched:.1%} of left rows matched within {tolerance}")
return out
sensor = pd.DataFrame({"time": pd.to_datetime(utc_frame["time"], utc=True) + pd.Timedelta("2min"),
"sensor_temp": utc_frame["temperature_2m"]})
model = pd.DataFrame({"time": pd.to_datetime(local_frame["time"]), "model_temp": local_frame["temperature_2m"]})
try:
temporal_join(sensor, model)
except ValueError as exc:
print(" ", exc)
wrong = model.assign(time=pd.to_datetime(model["time"]).dt.tz_localize("UTC")) # local labels taken as UTC
right = model.assign(time=pd.to_datetime(model["time"]).dt.tz_localize("Etc/GMT-1"))
for label, candidate in (("local labels as UTC", wrong), ("declared offset", right)):
joined = temporal_join(sensor.copy(), candidate)
agree = (joined["sensor_temp"] == joined["model_temp"]).mean()
print(f" {label}: values agree in {agree:.0%} of rows") right side has naive timestamps — localise before joining
100.0% of left rows matched within 5min
local labels as UTC: values agree in 17% of rows
converting right from Etc/GMT-1 to UTC
98.6% of left rows matched within 5min
declared offset: values agree in 99% of rows
The naive side is refused outright. Taken as UTC, the local labels still matched every row — an hourly series always has some reading within five minutes — but paired each sensor reading with the temperature from the wrong hour: the values agreed in 17% of rows, where they agree in 99% once the offset is declared. The match rate cannot catch that; comparing a value that should agree can.
Naive timestamps are an unlabelled unit
Why naive timestamps are an unlabelled unit
A naive timestamp is like a length recorded as "37". It is meaningless without a unit, and everything continues to work until two sources with different units meet.
The failure is silent because comparisons, sorts and joins all succeed on naive values. They just compare the wrong things. A sensor logging UTC and a survey app logging local time will interleave with a one-hour offset for half the year and zero for the other half.
Why daylight saving breaks sorting specifically
At the autumn transition the local clock goes 01:59 → 01:00. Data recorded continuously through that hour has local timestamps that go backwards.
Sorted by that column, an hour of data lands in the wrong place — the logger in step 4 recorded 01:00 twice. Track speeds go negative for a while and then extremely positive. Segmentation splits the track. Nothing raises until something tries to localise the column.
At the spring transition the local clock skips 01:00 → 02:00, so any timestamp inside the missing hour is invalid. tz_localize refuses these by default (nonexistent="raise"); shift_forward moves them, as step 3 showed.
Why UTC is not merely a convention
UTC has no daylight saving and no offsets, so the comparison operators mean what they appear to mean. That is why "store UTC, display local" is the standard pattern.
The one caveat: UTC does have leap seconds, and computer clocks generally do not represent them. For GPS analysis this matters only at the sub-second level, which is below the resolution of the timestamps in almost every trajectory file.
Why the boundary is the right place to convert
Converting at the edge of the system means exactly one function per source knows about that source's zone. Everything inside operates on one scale, and no downstream function has to ask.
Converting late, or per-operation, means the zone knowledge is spread across the codebase and each place can get it wrong independently. The measurable symptom is the one in Example 3: a join that matches every row, returns no error, and pairs readings an hour apart.
parse_dates, ambiguous times and IANA names
parse_dates=inread_csvis not a timezone strategy. With mixed offsets it left the column as strings; with one it gave a fixed offset. Follow it withto_datetime(..., utc=True).tz_localizeraises on ambiguous and nonexistent times by default. Keep that default; passambiguous=/nonexistent=only with a reason.- A backwards step of exactly −3600 s is a DST artefact, not unsorted data.
- Store UTC; convert for display only.
- Use IANA zone names (
Europe/London), never abbreviations likeBST, which are ambiguous across countries. - GPX times are UTC by specification and exporters do not always comply.
- Parquet round-trips timezones; CSV does not. A CSV export loses awareness unless the offset is written into the string.
merge_asofrequires both sides sorted and comparable — check the match rate.
Internal links
- How to load GPS tracks into Python as trajectories — parsing at the boundary
- My track has impossible speeds or teleports — negative intervals as a symptom
- How to clean and standardise date columns in spatial data — the general cleaning case
- Trajectories explained — why order matters so much
- How to clean a GPS track: outliers, duplicates and jumps — where this check belongs
- Sampling rate and gaps explained — intervals computed from bad times
- How to validate a GeoDataFrame against a schema before analysis — asserting timezone awareness
- How to record run metadata and data lineage in a GIS pipeline — recording which zone a source used
FAQ
Why are my track speeds negative?
Time going backwards. Either the rows are unsorted, or naive local timestamps crossed the autumn daylight-saving transition where the local clock repeats an hour.
Should I store timestamps in UTC or local time?
UTC. Convert to local only for display. Storing local time makes every join a guess.
What does ambiguous="raise" do?
It makes tz_localize fail rather than guess on the hour that occurs twice each autumn — and it is already the default in pandas, which raised Cannot infer dst time on this page's data. Pass another value only when you know which occurrence each row is.
Why does my temporal join return almost nothing?
Usually one side is UTC and the other is local time, so the two clocks differ by the offset — and with a generous tolerance it may instead match everything to the wrong hour, as example 3 did. Check both sides are timezone-aware and on the same scale, and compare a value that should agree, not just the match rate.
Is parse_dates in read_csv enough?
No. It produces timezone-naive timestamps. Follow it with pd.to_datetime(..., utc=True), or localise explicitly if the values are local.
How do I know if my column is timezone-aware?
Check the dtype. datetime64[ns] is naive; datetime64[ns, UTC] is aware.
Do leap seconds matter?
Not for trajectory work. They are a sub-second effect, well below the resolution of the timestamps in GPS files.