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_timezonereturns atimestamp with time zone. Athena views can’t store zoned timestamps, so cast back to plaintimestampat 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 nativetimezone(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
Top comments (4)
"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. Thewith_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_localvsscan_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?
"Put the assumption in the code, not in your head" is a better one-line summary than anything in my post. Thank you.
As for your question, it's documented today, not enforced. The conversion has a comment right where it happens in the view's SQL, and the view casts back to a plain timestamp that is UTC by convention. Nothing downstream checks that.
Both of your fixes are better than mine. Naming the column
scan_on_utcinstead ofscan_onmakes the contract visible in every query that touches it, and a silent reinterpretation turns into a schema break. I am making a note to add that in, honestly. And you're right about CI. I caught this by comparing Athena and DuckDB by hand, cell by cell, which worked once but isn't a test. A round-trip assertion that runs on Athena is on the list.Your
fetched_atvs. publish time story is the same bug in different clothes. Those are the worst ones, because every system involved is "correct."It is a good approach, and the DST fall-back catch is the kind of thing real data takes months to surface. One thing that has helped me: make the simulated MES output reproducible from a seed and plant rows on purpose at the DST boundaries and around midnight, so the same fixture runs against DuckDB and Athena in CI and any disagreement shows up as a diff instead of a surprise. How are you generating the plant data now, scripted or hand-built?
Thanks Muhammed, I really appreciate you coming back for another post!
It's all scripted. Crestline is a seeded simulation of the plant: jobs, machines, shifts, and operators run for 90 days, and the MES, ERP, and machine data all come out of that one run. Same seed, same output every time, which is what lets me keep an answer key.
I love the idea of planting rows at the boundaries on purpose. Funny enough, my 90-day window runs July through September, so it dodges DST completely. I'd been planning to tackle the fall-back hour in a later post, and a little fixture with scans around midnight and inside that repeated 1:00 to 2:00 AM hour, run through both engines, is a much better way to test it than waiting for it to show up. I'm adding it to the list, and I'll give you a shout when it lands.