SQL, data modeling, data quality, EDA, visualization · insight-1056
Disambiguate repeated local times with an offset or fold before parsing wall-clock timestamps
- Evidence type
- documented fact
- Difficulty
- hard
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- aviation, logistics, cross-industry
Summary: situation, action, reason, boundary
Situation
A gate-system export has local timestamps without offsets, and on the night clocks go back the hour 01:00-02:00 appears twice.
Action
Ask the source for UTC or an offset. If impossible, use a library that exposes the ambiguity (fold in Python's datetime), mark ambiguous and non-existent local times explicitly, and resolve them with a documented rule using a sequence field. Count how many rows fall in transition hours.
Reason
A repeated local time maps to two UTC instants, and a skipped local time maps to none, so a wall-clock string alone does not identify an instant.
Boundary
Logs and exports from devices that write local time, spreadsheets, and legacy scheduling systems.
Symptom or risk
Parsing assigns both occurrences to the same instant, whichever default the library applies, so events from two different hours collapse, durations go negative and ordering within the night is wrong.
Exceptions
Zones without daylight saving, or timestamps already stored in UTC.
Trade-offs
Extra columns and review for a handful of rows per year.
Pitfalls
Dropping the rows because they raise errors, which removes the busiest night-time events.
Lessons and concepts
Claims and their sources
PEP 495 adds a fold attribute that distinguishes the first and second occurrence of a repeated local time, and defines which UTC offset is used for local times that do not exist in a forward gap.