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

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

• The SQL, both the broken version in the comment and the fix: margin_gap.sql
• The DuckDB macro that makes the SQL portable: sources.sql
• The DST transition test: test_shifts.py

Comments

[Yuhe He]

"Same line of SQL. Two engines. Six hours apart. No warning on either side." — this is the scariest bug class because the test suite passes by construction: your laptop's engine is the one you test against, so the lenient semantics never get exercised where they cost money.

I hit the same family from the data-collection side: my dumps carry a per-row fetched_at (crawl timestamp), and consumers keep reading it as the post's publish time. Same root cause — a value whose meaning depends on an engine/consumer assumption that's nowhere in the schema. The with_timezone(value, zone) AT TIME ZONE 'UTC' discipline is really "put the assumption in the code, not in your head."

Two things that made our pipeline survive this: (1) a round-trip test that asserts the conversion on the production engine (CI runs Athena/Presto semantics, DuckDB stays a dev convenience — a laptop-only suite structurally cannot catch engine-specific semantics), and (2) naming columns after their semantics, not their type (scan_on_local vs scan_on_utc) so a silent reinterpretation becomes a visible schema break.

Curious: since Athena views can't store zoned timestamps, the cast-back erases the zone at rest — do you keep the explicit conversion documented in the view DDL itself (comment), or is the "this column is plant-local" contract enforced somewhere downstream?