Athena read my timestamps as UTC. It didn’t tell me.

In a tale of bugs and timing, a reporter found a fake metal fabrication plant that exposed a hidden issue in database behavior. The MES in the plant recorded timestamps in local time, with no time zone specified. To convert these timestamps to UTC, the reporter used SQL to scan the local time and convert it accordingly. However, the same SQL line, when executed in two engines - DuckDB and Trino, produced two different answers, six hours apart. The root of this discrepancy lay in how the engines interpreted the timestamps.

DuckDB, being more lenient, assumed the value was in UTC if it didn't already carry a time zone. This leniency allowed the reporter to write sloppy SQL on his laptop, which later turned out to be wrong in production. On the other hand, Trino followed the SQL standard and assumed the value was in UTC when no time zone was provided.

The fix to this problem was to explicitly state the time zone the value was in before converting it. The reporter used the with_timezone function to achieve this, but faced two gotchas on the Athena side. First, with_timezone returns a timestamp with time zone, and Athena views couldn't store zoned timestamps. So, the reporter cast back to plain timestamp at the view boundary.

Second, if the SQL was to run in both engines, he defined a DuckDB macro that aliased with_timezone(ts, zone) to DuckDB's native timezone(zone, ts). This way, one line of SQL could work everywhere.

The significance of this issue was clear - a six-hour difference could mean a whole shift's worth of work being lost. For instance, if the conversion was wrong, the second shift's downtime could fall on the first shift's report. The reporter wrote a test specifically for the boundary where daylight saving time ends in Mountain Time, which caught a sneaky off-by-one error due to the fall-back.

Thus, if one isn't testing against DST transitions, they might have a bug and not even know it. The SQL provided for the broken version and the fix, as well as the DuckDB macro that made the SQL portable, were included in the report for reference.