TLDR: If your daily sales chart shows weekend zeros on warehouse-fulfilled orders, you're almost certainly bucketing by the wrong timestamp — and your timezone is probably wrong too.

the problem

We've been building a full supply-chain dashboard for an ecommerce business — sales mix, inventory velocity, burn-down forecasts, the works.

The Daily Sales chart went live and immediately looked broken.

Weekends: zero bottles sold.

I flagged it right away: "still seeing days with ZERO bottles sold — this is inaccurate."

My first instinct was missing rows in the database.

I was wrong.

what I chased first

I ran a diagnostic query against those zero days.

Rows existed. Five to seven line items per day, in fact.

But every single one had product_id IS NULL — ship-protection items, gift cards, exactly the kind of thing that gets filtered out of every reportable-SKU chart by design.

So the chart wasn't broken. The chart was technically correct.

The problem was the column I was bucketing on. Which, I'll admit, took me an embarrassingly long time to see.

the two bugs (there were always two)

Bug #1 — wrong key.

The Sales chart was bucketing by fulfilled_at — the timestamp when the ecommerce business's 3PL (the outside warehouse that actually ships boxes) put the order on a truck.

The 3PL runs Monday through Thursday.

Saturday fulfilled_at = near zero. Obviously. Nothing ships Saturday.

But orders come in every single day, including weekends. The fix: bucket the Sales chart by shopify_created_at, the moment the customer actually placed the order. Now weekends look like weekends, because that's when the intent happened.

Bug #2 — wrong timezone.

Postgres buckets timestamptz → date using the server's local timezone. Supabase servers run UTC. So a 9pm ET order becomes a 2am UTC timestamp — and lands on the next calendar day.

The business is an Eastern Time business. Every single report had been quietly off by hours.

The fix is two lines, but it has to happen in two places:

  1. SQL (migration 034): Every timestamptz-to-date cast needs the shift — (li.shopify_created_at AT TIME ZONE 'America/New_York')::date AS day. Apply it to fulfilled_at, cancelled_at, the lot.

  2. JS (src/lib/datetime.ts): new Date('2026-05-19') parses date-only strings as UTC midnight. In ET that renders as May 18. The fix: parseLocalDay appends T00:00:00 before parsing, which forces the local-timezone interpretation instead.

(One thing you DON'T touch: comparison clauses like >= p_as_of - interval '30 days'. Those are absolute-time arithmetic — TZ-safe by construction. Only the date-bucketing cast needs the shift.)

the rule I'm keeping

Match the bucketing key to the chart's framing. Full stop.

Sales / Revenue charts → shopify_created_at. The customer's intent is the date that matters.

Velocity / Throughput / Fulfillment charts → keep fulfilled_at. That's ops cadence — it's supposed to look spiky by day. Just default the 7-day trailing-avg smoothing toggle ON so Monday doesn't look catastrophic after a quiet weekend.

And anchor everything to the business's actual timezone before you bucket. UTC is a storage convention. It's not how your operations team reads a calendar.

I shipped a dozen charts before catching this. Every one of them was silently bucketing in the wrong timezone. The numbers weren't dramatically wrong… just consistently, subtly off. That's the sneaky kind of bug — the chart looks plausible right up until someone who knows the real numbers takes a hard look.

Worth getting exactly right.