Before this: Compare instants, not timestamp text
The idea
An instant is a point on the global timeline. 2026-01-02T03:00+02:00 and 2026-01-02T01:00Z are the same instant written in two offsets. Comparing their text gives the wrong order; comparing instants gives the right one.
Store instants in UTC (or with their offset) and convert to a viewer's time zone only when displaying. Grouping events by local day needs the zone of the people involved, so keep it as data when it matters.
Date-only values such as birth dates are not instants. Converting them to midnight UTC and displaying them in another zone shifts the day.
Worked example
Input: ts: 2026-01-02T03:00+02:00, 2026-01-02T01:30Z
- Visual
Add step → Convert type → column ts → Timestamp with time zone
- SQL (DuckDB)
CAST(ts AS TIMESTAMPTZ) -- DuckDB compares instants after the cast
- pandas
pd.to_datetime(df["ts"], utc=True)
Result: 01:00Z comes before 01:30Z, although the text '03:00' sorts after '01:30'
Common mistakes
- Sorting timestamp strings.
- Assuming the server's zone.
- Turning date-only values into instants.
Check yourself
Why not store local time text?
Local text without an offset can't be compared across zones and is ambiguous on DST changes.
Practice it
Keep timestamps that carry an offset and quarantine the rest.