The reconciliation had been off by a handful of records every day for months, and every month somebody adjusted it by hand and moved on. It was only when the discrepancy jumped after a clock change that anyone looked properly, and what we found was that the platform had at least four different ideas about what a timestamp was, all of them stored in columns with the same name.
The web application wrote UTC, correctly. A batch loader that predated it wrote server local time, which had been UTC for years because the server sat in a data centre we later migrated out of, so the values silently changed meaning mid-history. A third-party feed sent times in the customer's local zone with an offset that the parser was discarding. And an internal tool wrote a date with no time at all, which the database happily stored as midnight, in whichever zone the connection happened to be using that day.
None of this produced errors. Every value was a valid timestamp. Reports mostly balanced because most activity happened in the middle of the working day, where an hour of ambiguity does not cross a date boundary. The failures clustered exactly where you would expect once you know: late evening transactions, the two weekends a year when clocks move, and anything measuring a duration between two systems, which could come out negative and did.
Fixing the storage was the easy half. Everything is UTC with an explicit zone recorded separately when local time is part of the meaning, because "when the customer says it happened" and "when it happened" are different facts and both matter. The harder half was the history. We could not retroactively know what an ambiguous value meant, so we marked the affected range as lower confidence and documented it rather than pretending a backfill had made it true.
What I now watch for is not time zones specifically. It is any column where two systems write the same field and nobody has written down what the field means. The database will not stop you. The types will match. The data will look fine right up until the day somebody subtracts one of your values from another and gets an answer that cannot possibly be right.
– Serguey Shinder
Top comments (0)