Nobody gets an error. Somebody gets blamed.

I built a fake metal fab plant to find this kind of bug before a real customer does. Last week it caught one.

The setup

The MES in the fake plant writes timestamps the way real MES systems do: local time, no time zone. 14:30 means 2:30 PM at the plant. The plant is in Mountain Time.

I wrote the obvious SQL to convert plant-local timestamps to UTC:

scan_on AT TIME ZONE 'America/Denver'

DuckDB, running locally, read that as: “this value is in Denver time; give me the UTC moment that corresponds to it.” Correct for what I meant.

Athena read that as: “this value is UTC; give me the Denver moment that corresponds to it.” Also correct, but for a different interpretation.

Same line of SQL. Two engines. Two answers. Six hours apart. No warning on either side.

Why

Trino’s behavior is the SQL standard: AT TIME ZONE says what zone to convert to. If the value doesn’t already carry a time zone, it’s assumed to be UTC. DuckDB is more lenient. In this case, DuckDB’s leniency is what let me write sloppy SQL on my laptop and have it quietly change meaning in production.

Trino isn’t the one being weird. DuckDB is.

The fix

Say explicitly what time zone the value is IN, then convert:

with_timezone(scan_on, 'America/Denver') AT TIME ZONE 'UTC'

Two gotchas on the Athena side:

  • with_timezone returns a timestamp with time zone. Athena views can’t store zoned timestamps, so cast back to plain timestamp at the view boundary.
  • If you want the same SQL to run in both engines, define a DuckDB macro that aliases with_timezone(ts, zone) to DuckDB’s native timezone(zone, ts). Then one line of SQL works everywhere.

Why it matters

In a plant, six hours is a whole shift. Get the conversion wrong and second shift’s downtime lands on first shift’s report. Nobody gets an error. Somebody gets blamed.

Bonus: it’s six hours in summer and seven in winter. Daylight saving time ends in Mountain Time on November 1. I wrote a test specifically for that boundary, and it caught a sneaky off-by-one on fall-back. If you aren’t testing against DST transitions, you have a bug and don’t know it yet.

Code