Before this: Find the row grain before removing duplicates
The idea
"2026-02-01T01:00:00+01:00" and "2026-02-01T00:00:00Z" are the same moment. As text, the first sorts after the second, so a "latest record" rule picks the wrong row.
Parse timestamps into an instant type with the offset applied (timestamptz in SQL, UTC datetimes in pandas), then sort or compare.
A timestamp without an offset is not "probably UTC". Unless the rules give a time zone, it is ambiguous and belongs in exceptions.
Worked example
Input: updated: "2026-02-01T01:00:00+01:00", "2026-02-01T00:30:00Z"
- Visual
Convert updated to timestamptz (stop on bad values) → order by updated descending
- SQL (DuckDB)
ORDER BY CAST(updated AS TIMESTAMPTZ) DESC
- pandas
pd.to_datetime(df["updated"], utc=True, format="ISO8601")
Result: 00:30Z is later than 01:00+01:00 (= 00:00Z)
Common mistakes
- Sorting timestamp strings.
- Assuming UTC for naive timestamps.
- Dropping the offset during parsing.
Check yourself
Is 01:00+01:00 later than 00:30Z?
No. It is 00:00Z, thirty minutes earlier.
What do you do with "2026-01-15 09:00" (no offset)?
Quarantine it unless the rules name a time zone.
Practice it
Pick the survivor by the latest instant, then priority, then id.