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

Tim Collinson — Tue, 13 Oct 2026 21:19:36 +0000

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

Your ERP says the job was profitable. Your machines disagree.

Tim Collinson — Thu, 01 Oct 2026 17:29:58 +0000

What you'll get out of this

In about 12 minutes, you'll walk away with:

Why your ERP can hide a money-losing machine, in plain terms. Standard costing charges machine time at standard hours, so long setups vanish.
A working pattern for joining ERP, MES, and machine data on AWS: raw files in S3, cataloged in Glue, queried with Athena, deployed with one CDK stack.
The gotchas that cost us time, so they don't cost you any: Athena time zones, JSON SerDe key casing, 16-bit counters, and a cdk diff that isn't read-only.
Three questions to take to your own plant this week.
A repo you can deploy in your own AWS account for a fraction of a cent, and tear down cleanly.

Plant leaders: read the story, "Why the ERP can't see it," and the three questions at the end. Builders: jump straight to "The rebuild."

The margin report says everything is fine

Let's talk about the monthly margin review. Why would I write about a finance meeting on a technical blog? Great question. Stick with me; there's SQL at the end of this rainbow.

Picture it. Finance puts up the job cost report, and the bracket and enclosure work is holding its numbers. Everyone nods.

The plant manager doesn't nod. She knows second shift on the older press brake is a mess. Setups drag, jobs finish late, and the operators on that shift are always fighting the machine. But the report says those jobs made money, and she has no numbers to argue with. Nothing she can put on a slide will beat the ERP.

If you've worked in a mid-market plant, you've probably sat in that meeting. I have sat in versions of it for years, from pushing plant data into a PI historian at BP to building APIs for manufacturing software. (If you don't know what a PI historian is, count yourself lucky.) The plant manager is usually right. The problem is that proving it means joining three systems that were never designed to talk to each other.

So we built a plant, gave it that exact problem, and set out to prove it.

TL;DR

We simulated 90 days at Crestline Supply, a fictional metal fabricator: 1,252 finished jobs across ERP, MES, and press brake data, each realistically imperfect.

The ERP overstates margin on one brake's second-shift jobs by 4.4 points against the true figure, about $5,800 a quarter from a single machine on a single shift. Setups there run 2.84 times standard, and the ERP can't see it.

The ERP is wrong everywhere else too, but in the pessimistic direction. It's only flattering where the real problem lives.

We rebuilt job cost from all three sources with one SQL file running on Amazon Athena. It lands within 1% of the true cost for 91.9% of jobs.

Everything is open source and runs in your own AWS account. The Athena spend for the whole project was about two-tenths of a cent. https://github.com/trcollinson/crestline-supply

Meet Crestline Supply

Crestline Supply doesn't exist. I made it up. That's the point. It's a simulated metal fabrication plant that makes electrical enclosures, mounting brackets, and custom panels for HVAC and industrial customers. It runs two shifts. Parts go through a fiber laser, deburring, two press brakes, hardware insertion, powder coat, and assembly.

Why simulate instead of using real data? Because real plants never know the truth. When your rebuilt numbers disagree with the ERP, you can't tell who's right. In a simulation, you can.

The simulator works in two layers:

Ground truth. A discrete-event model records what actually happened: every setup, every stroke, every scrapped part, every operator's real time. This is the answer key, and it stays hidden.
Three imperfect views. From that truth, the simulator produces what each business system would actually record, complete with the gaps and distortions real systems have.

The analysis only ever sees the imperfect views. A test in the repo enforces that the query code can't read the answer key. The answer key is used for one thing: grading our work at the end.

The data shapes follow patterns common across mid-market ERPs and PLCs. If you've worked in these systems, they'll look familiar.

Three systems, three partial truths

Every system at Crestline is telling the truth about something. None of them is telling the whole truth.

System	What it knows	What it doesn't know	How it reports
ERP	The job, quantity, price, and standard cost	How long setup and run actually took	Labor rounded to 0.1 hr, posted the next day; machine burden at standard hours
MES	Who scanned onto which job, and when	Setup vs. run; whether the operator was really working	Nightly CSV files in local time with no time zone; about 3% of scans never closed
Press brakes	When each stroke happened	Which job it belonged to	PB-01: JSON on every change. PB-02: a 16-bit counter polled every 30 seconds that wraps

The answer to the plant manager's question lives in the gaps between these three. The machines know when setup ended. The MES knows who was on the job and for how long. The ERP knows what it was worth. Nobody has joined them.

Why the ERP can't see it

The ERP isn't broken. It's doing exactly what standard costing is designed to do, and that design hides long setups.

A job's cost has three main parts: material, labor, and burden. Burden is the cost of running the machine itself: depreciation, power, maintenance, floor space. At Crestline, the ERP's own work center rates put an hour on a press brake at $85 of burden and $38 of labor. The burden is the bigger number.

Here's the catch. Crestline's ERP, like most, takes actual labor from the MES but applies burden at standard hours. If a setup is supposed to take 20 minutes, the ERP charges 20 minutes of machine time, no matter how long the machine actually sat in setup.

Walk through one setup on PB-02, second shift, running 36 minutes over standard:

The operator stays scanned in the whole time, so the extra 36 minutes of labor (about $23) eventually reaches the ERP.
The extra 36 minutes of machine time (about $51 of burden) never does.

More than two-thirds of the cost of that overrun is invisible to finance. Finance isn't hiding it from you. The ERP is hiding it from finance. Now multiply. Over the quarter, PB-02 ran 179 setups on second shift, averaging 43 minutes over standard. That's about 128 machine-hours and roughly $10,900 of burden the ERP never charged. Even elsewhere in the plant, the ERP only sees about 90% of true burden, from ordinary setup variation and downtime. On PB-02 second shift, it sees 72%. A real problem turns into a healthy-looking margin.

There's a second effect pulling the other way. Operators stay scanned in through breaks, forgotten scans are only trimmed back to the end of the shift, and nobody scans off during downtime. So everywhere in the plant, the ERP charges about a third more labor than anyone actually worked. Labor is the smaller cost, but over-reporting it still charges each job more than it should, so margins look 2 to 4 points pessimistic. Keep that in mind; it matters for the results.

The rebuild

The rebuild is one SQL file with six commented views and a final select. It runs on Amazon Athena, directly over the raw files in S3.

Each system hands the rebuild a different piece of the story. Four steps in one Athena query join them, and the join surfaces the one place the ERP flatters itself.

The raw files land in S3 exactly as each system would have produced them, untouched. That matters: when you find a mistake later, you can always reprocess from the original.

Step 1: Put everything on one clock

The MES writes local wall-clock time with no offset. The ERP and machines use UTC. Before anything can be joined, every timestamp needs to mean the same instant.

This is where the first surprise showed up. scan_on AT TIME ZONE 'America/Denver' looks like it says "this is plant time." In Athena, it doesn't. Athena reads a bare timestamp as UTC and converts it, so 2:30 PM comes back six hours early (seven in winter). No error, just a wrong answer. We only caught it because our local test engine read the same line the opposite way.

The fix is with_timezone(), which states which zone the value is in. Athena views also can't store a zoned timestamp, so each view casts back to plain UTC:

-- The MES writes local time with no offset, so the conversion has to say
-- which zone it is in: with_timezone(). Not `at time zone`, which the two
-- engines read differently: DuckDB takes a bare 14:30 as plant time
-- (20:30 UTC), but Athena takes it as UTC and converts, six hours early.
select record_id,
       employee_id,
       job_num,
       op_seq,
       work_center,
       cast(with_timezone(scan_on,  'America/Denver') at time zone 'UTC' as timestamp) as scan_on,
       cast(with_timezone(scan_off, 'America/Denver') at time zone 'UTC' as timestamp) as scan_off
from fixed;

Step 2: Figure out which brake did the work, and when setup ended

The brakes don't know what job they're running. But a job's brake operation has an MES scan window, and a brake produces strokes. The first stroke after an operator scans on marks the end of setup.

PB-02 makes this harder. PB-02 is old. PB-02 is grumpy. PB-02 gives you a 16-bit stroke counter, polled every 30 seconds, and nothing else. The counter rolls over at 65,535 (ours wrapped once, on August 26 at 2:14 PM), so the strokes since the last poll are the difference mod 65,536:

-- PB-02's counter is 16 bits wide and rolls over at 65,535, so the
-- strokes since the last poll are the difference mod 65,536.
select 'PB-02' as brake,
       gw_ts   as counted_at,
       (cnt - lag(cnt) over (order by gw_ts) + 65536) % 65536 as strokes
from pb02_counts

That handles rollover, not a counter that resets to zero when someone power-cycles the machine. A reset looks like a wrap and would count phantom strokes. That mess shows up in a later post.

The 30-second poll caused a subtler bug. PB-02 reports a stroke up to 30 seconds after it happens, so the previous job's last stroke often landed just after the next operator scanned on. Setups looked instant. The fix is to look one poll late at both ends of the scan window.

Then there's the question in this step's title: which brake? The query counts each brake's strokes during the scan window and picks the brake whose count comes closest to the pieces the operator reported. Ties go to the brake whose last stroke came nearest the operator's scan-off:

-- PB-02 reports strokes up to 30 s late: look 30 s late at both ends.
join brake_strokes as b
  on b.counted_at >  o.started_at + interval '30' second
 and b.counted_at <= o.ended_at   + interval '30' second

-- Closest stroke count to the reported pieces wins.
-- On a tie, the brake whose last stroke was nearest scan-off.
row_number() over (
    partition by o.job_num, o.op_seq
    order by abs(s.strokes - o.pieces),
             abs(date_diff('second', s.last_stroke_at, o.ended_at))
) as rank

The full version is in queries/margin_gap.sql.

Against the answer key, all 935 brake operations were placed, 933 on the right brake and all 935 on the right setup shift. The two misses are two separate exact ties: a pair of 16-piece jobs and a pair of 46-piece jobs, each pair running at the same time, one job on each brake, so the counts matched either way.

Here's one real second-shift job from the run. Tasha scanned onto J100795 at 7:36 PM, and PB-02 didn't make its first stroke until 8:32. The ERP charged 20 minutes of setup; the shaded stretch is the machine time it never sees.

Step 3: Clean up the labor the way the plant should

About 3% of MES scans are never closed. Someone forgets to scan off, and a supervisor closes the scan at 06:00 the next morning. Posted as-is, a 20-minute operation becomes a 14-hour one. (Nobody bends brackets for 14 hours straight. Nobody.)

Crestline handles this the way many plants do: a supervisor's labor exception report trims forgotten scans back to the end of the shift. That's better than nothing, but it still charges hours the operator spent on other work.

The query does one thing the supervisor can't do by hand across thousands of rows: it caps each scan at the same operator's next scan-on. If Tasha scanned onto another job at 3:10, she wasn't still on the first one at 11:00. That single rule is most of the difference between the ERP's labor and the rebuilt labor. And notice who was right all along: the supervisor already knew this data was garbage, and the exception report is a workaround someone built because the systems failed them. With the right join, their daily forensics becomes something the query does continuously, on every job.

Step 4: Recompute cost the way it actually happened

With clean hours and real setup times, cost is straightforward: actual labor hours times the labor rate, actual machine hours times the burden rate, plus material. Then compare the ERP's margin to the rebuilt margin, grouped by brake and by the shift the setup happened on.

What the rebuild found

The ERP is wrong about every group of jobs in the plant. It's only flattering in one place, and that's exactly where the problem is.

Margin by brake and setup shift, 90 days, 1,252 finished jobs:

Brake and shift	ERP margin	Rebuilt margin	True margin	Setups vs. standard
PB-01, 1st shift	24.3%	25.5%	27.1%	1.06×
PB-01, 2nd shift	25.9%	27.8%	28.6%	1.07×
PB-02, 1st shift	23.0%	23.7%	25.0%	1.14×
PB-02, 2nd shift	20.4%	14.8%	16.0%	2.84×
No brake operation	23.6%	26.7%	27.7%	—

"True margin" comes from the hidden answer key. In a real plant you'd have the other three columns and not that one. If you run the query yourself, its gap column shows 5.7 points for PB-02 second shift. That's the ERP against the rebuild, the gap a plant manager would actually see; the 4.4 points in this post is the ERP against the truth.

Three things to notice:

Everywhere else, the ERP is pessimistic. It understates margin by 2 to 4 points against the truth, because time nobody worked gets charged as job labor. A finance team that learns to discount the ERP a little would be right almost everywhere.
On PB-02 second shift, it flips. The ERP overstates margin by 4.4 points against the truth. Measured against the plant's usual pessimistic bias, the real gap is bigger than it looks. A finance team that corrected the ERP up by its usual 3 points would put this group at 23.4% against a true 16.0%, a 7.4-point miss. On that group's roughly $131,000 of quarterly revenue, the overstatement is worth about $5,800 a quarter, or roughly $23,000 a year, from one machine on one shift.
The rebuild errs conservative. It lands 1.3 points below the truth on PB-02 second shift, and below the truth in every group by 0.8 to 1.6 points, because the few scans it can't fully clean err high, not low. That's the direction you want a management number to miss in.

Grumpy PB-02, second shift, exactly where the plant manager knew to look. That's the conversation she needed. Not "second shift feels slow," but "setups on PB-02 second shift run 2.84 times standard, and the ERP can't see it because burden is charged at standard hours."

How we know it's right

The rebuild lands within 1% of the true job cost for 91.9% of jobs. The ERP's own "actual" cost does that for 14.9%.

Because we have the answer key, we can grade the rebuild the way you never can in a real plant. An evaluation script compares every job's rebuilt cost to its true cost:

Measure	Rebuilt cost	ERP "actual" cost
Jobs within 1% of true cost	91.9%	14.9%
Jobs within 5% of true cost	94.6%	53.0%
Median error	0.03%	4.52%

The more useful part is the misses. All 101 jobs outside 1% are explained: 91 are forgotten scan-offs the query can't fully clean, 9 are operators who stepped away during downtime while still scanned in, and 1 is a machine sitting idle while scanned. None are unexplained.

One honest note: the first version of the simulator was too clean. The rebuild matched the truth almost perfectly, which told us the plant wasn't messy enough to be believable. Adding forgotten scan-offs made it realistic, and that's also when we had to model the supervisor's exception report, because posting those scans as-is wrecked the ERP's numbers in a way no real plant would tolerate.

What building it on AWS taught us

The AWS side is deliberately small: an S3 raw bucket, an Athena results bucket, a Glue database, and an Athena workgroup with a 1 GB scan cap, all deployed by one CDK stack. Small didn't mean free of surprises. It never does.

Athena reads a bare timestamp as UTC. Covered above, and the most likely thing to bite anyone working with plant data. Use with_timezone() before AT TIME ZONE.
The OpenX JSON SerDe lowercases keys, even inside nested objects it returns as text. JSON paths are case-sensitive, so fields silently came back null. The fix was reading each PB-01 message as a single text line and using json_extract_scalar. PLC tag names like N7:12 weren't going to be valid Glue column names anyway.
Partition projection didn't fit. The ingest_date partition is dated October 1, the extract date, which was still in the future when we built this. A projection range ending at NOW skipped it, so setup runs MSCK REPAIR TABLE instead.
Plain cdk diff isn't read-only. It uploads the template and creates a change set. (Yes, really. I was surprised too.) Use cdk diff --method=template when you want a purely local comparison.
Stack tags changed. With the @aws-cdk/core:explicitStackTags feature flag, Tags.of() no longer sets stack tags, so tags are applied both ways on purpose. A Glue database can't be tagged through CloudFormation at all.
Idempotent uploads came almost free. For a single-part upload with SSE-S3, the ETag is the content's MD5, so rerunning the upload skips unchanged files. That wouldn't hold with KMS encryption or multipart uploads.

Every one of these is small. Together they're the difference between a demo that works on my machine and one that works in yours.

Run it yourself

The whole thing deploys into your own AWS account in a few minutes, costs well under a dollar, and tears down cleanly. The simulation is deterministic, so you'll get exactly the numbers in this post.

You'll need: uv, Node.js with the CDK CLI at version 2.1143 or later, the AWS CLI, and an AWS profile with a region configured.

Deploy and run:

git clone --branch post-01-margin-gap https://github.com/trcollinson/crestline-supply.git && cd crestline-supply
export AWS_PROFILE=<profile>
uv sync
(cd infra && cdk bootstrap && cdk deploy)
uv run crestline sim backfill --days 90 --end 2026-10-01 --seed 42 --upload
uv run crestline athena setup
uv run crestline query margin-gap --engine athena

cdk deploy stops once to ask you to approve the IAM role it creates. Type y and it carries on.

Then try the experiment that makes the point. Set enabled: false under pb02_second_shift_setups in scenarios.yaml, rerun the backfill with --upload, and rerun the query. PB-02 second shift comes back at 25.2% in the ERP and 25.5% rebuilt, with setups at 1.07× standard, right in line with everything else. Bonus detail: with the scenario off, PB-02 second shift handles 219 jobs instead of 174. The slow brake was also doing less work, because each job goes to whichever brake frees up first.

No AWS account handy? The README has a local option.

Cost: the upload is 99 files and 46.6 MB, so storage rounds to zero. Each query run scans about 100 MB, around a twentieth of a cent, so rerun as much as you like. Athena spend for the entire project, including every test query, was about $0.002.

Teardown:

(cd infra && cdk destroy)
aws logs describe-log-groups --log-group-name-prefix /aws/lambda/CrestlineFoundation --query 'logGroups[].logGroupName'
aws logs delete-log-group --log-group-name <name from above>

cdk destroy removes the stack and empties the buckets. The two aws logs commands clean up the log group CDK's auto-delete Lambda leaves behind. If you bootstrapped CDK just for this, also delete the CDKToolkit CloudFormation stack, then empty and delete the staging bucket it keeps.

What this means for your plant

You don't need a perfect data platform to find this kind of problem. You need the join, and you need to trust it.

Three questions worth asking your team this week:

Do we know actual setup time by machine and shift, or only standard? If the answer is "standard," your long setups are hiding in your margins right now.
Can we connect a machine's output to the job it was running? Even a dumb stroke counter and an MES scan window are enough to answer "when did setup actually end?"
Does finance know burden is applied at standard hours? Most people outside cost accounting have never thought about it, and it's the reason the ERP looked healthy here.

If the answers are "no," you're in good company. That's most mid-market plants I've worked with. The good news is that the data to answer them is usually already sitting in systems you own. Go find it. Do it this week.

What's next

This post cheated a little. The ERP data arrived as a clean bulk export. In real life, getting data out of a mid-market ERP means tokens that expire mid-extract, paging that shifts under you, and a LastModified field that doesn't always get updated. That's the next post in the Crestline series, in two weeks.

Tim Collinson has spent nearly 30 years building software, much of it on AWS, across manufacturing, oil and gas, and enterprise IT. Reach him at trcollinson@gmail.com.

P.S. If you deploy it and your numbers don't match mine, open an issue. I want to know. Deterministic simulations are only fun until they aren't.

DEV Community: Tim Collinson