🔧 Fix Movement & time Data cleaning Movement & Time · step 5 of 14 Updated

My Time Column Sorts or Joins Wrongly

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.

Quick answer

Parse to UTC at the boundary, and never store a naive timestamp:

import pandas as pd

# from ISO 8601 strings with offsets, or Z, or a mixture
df["time"] = pd.to_datetime(df["time"], utc=True, format="ISO8601")

# from genuinely local times whose zone you know
df["time"] = (pd.to_datetime(df["local_time"])
                .dt.tz_localize("Europe/London",
                                ambiguous="raise", nonexistent="raise")
                .dt.tz_convert("UTC"))

ambiguous="raise" and nonexistent="raise" are the important arguments. Without them, pandas silently picks an interpretation for the hour that occurs twice each autumn and the hour that does not exist each spring.

Three timezone failures: a repeated hour at the autumn transition, a missing hour in spring, and mixed offsets sorting into the wrong order.
All three produce data that looks fine row by row and is wrong as a sequence.

Step-by-step solution

1. Find out what you actually have 

print(df["time"].dtype)
  • object — 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 

df = pd.read_csv(path, parse_dates=["time"])       # naive, and silently so
df["time"] = pd.to_datetime(df["time"], utc=True)  # aware

If the strings carry offsets, utc=True converts them to a single scale. If they 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=True picks the first (DST) occurrence.
  • Spring, when clocks go forward: 01:30 local never happens. nonexistent="raise" refuses; nonexistent="shift_forward" moves it.

Defaulting to raise 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 

def check_time_order(df, id_col="track_id"):
    for key, sub in df.groupby(id_col):
        dt = sub["time"].diff().dt.total_seconds()
        negatives = int((dt < 0).sum())
        if negatives:
            print(f"  {key}: {negatives} backwards steps, "
                  f"largest {dt.min():.0f}s")

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:

df["local"] = df["time"].dt.tz_convert("Europe/London")

Local time is a presentation format. Storing it makes every downstream join a guess.

Local times converted to UTC at input, all processing in UTC, and conversion back to local only for display.
Convert at the boundary. Everything inside the pipeline is UTC.

Code examples

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"
                            if series.astype(str).str.contains("T").any() else None)

    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")

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

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}")
    if matched < 0.5:
        print("  ! low match rate — check that both sides are on the same "
              "clock and that the tolerance is realistic")
    return out

An as-of join between a UTC series and a series that is actually local time will match at most a handful of rows in summer and none in winter. Reporting the match rate turns that into a visible warning instead of an empty result.

Explanation

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. Track speeds go negative for a while and then extremely positive. Segmentation splits the track. Nothing raises.

At the spring transition the local clock skips 01:00 → 02:00, so any timestamp inside the missing hour is invalid. tz_localize with nonexistent="raise" catches these; the default shifts them silently.

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 4% of rows and returns no error.

A temporal join matching 97 percent when both sides are UTC and almost nothing when one is local time.
The winter case returns nothing and raises nothing. The match rate is the only signal.

Edge cases or notes

  • parse_dates= in read_csv gives naive timestamps. Follow it with to_datetime(..., utc=True).
  • ambiguous="raise" and nonexistent="raise" turn two silent corruptions a year into failures.
  • 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 like BST, 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_asof requires both sides sorted and comparable — check the match rate.

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. The default guesses, silently.

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. Check both sides are timezone-aware and on the same scale, and report 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.