How Four Conflicting Timestamp Formats Silently Corrupted Months of Data

A data reconciliation system had been producing small daily discrepancies for months, with errors manually corrected each time without deeper investigation. The root cause was discovered only after a daylight saving clock change caused a larger spike: four separate systems were all writing to identically named timestamp columns but using different time zone conventions. The web app used UTC, a legacy batch loader used server local time that silently shifted meaning after a data centre migration, a third-party feed's time zone offset was being discarded by the parser, and an internal tool stored dates with no time, defaulting to midnight in whatever zone the database connection used. Errors went undetected because most activity fell mid-day, where an hour's ambiguity rarely crosses a date boundary — failures clustered around late-evening transactions, daylight saving weekends, and cross-system duration calculations. The team resolved the issue by standardising all new storage to UTC, separately recording local time where relevant, and flagging the ambiguous historical data range as lower confidence rather than attempting an unreliable backfill.