How a Timezone Bug Silently Corrupted Three Months of Analytics

Nobody reported this. That is the part worth dwelling on.

The daily orders dashboard showed plausible numbers. Traffic went up on weekends, dipped on Tuesdays, spiked during campaigns. It looked exactly like what you would expect a business to look like, and it had been wrong for three months.

It was found because a finance colleague was reconciling a monthly total against the payment provider's report and was off by about 1.4 percent. Not off by a rounding error, and not off by a large obviously-broken amount. Off by an amount small enough that the first three people who looked assumed it was a fee calculation.

The setup

Orders are stored with created_at timestamptz, which is correct. Postgres stores it as UTC internally and converts on the way in and out based on the session timezone.

The reporting query grouped by day:

SELECT date_trunc('day', created_at) AS day, count(*), sum(total_paise)
FROM orders
WHERE created_at >= now() - interval '90 days'
GROUP BY 1 ORDER BY 1;

That looks fine. It is the query almost everybody writes.

The problem is that date_trunc('day', created_at) on a timestamptz truncates in the session's timezone, and the session timezone was UTC, because the reporting service ran in a container with no TZ set.

The business is in India. IST is UTC plus 5:30.

What that actually does

An order placed at 02:00 IST on 15 May is 20:30 UTC on 14 May. Truncated in UTC, it lands on 14 May. In the business's actual day, it belongs to 15 May.

So every order between 00:00 and 05:30 IST was being attributed to the previous day.

For most days this roughly cancels out. You lose the early morning of today and gain the early morning of tomorrow, so daily totals are close. Close enough that nothing on the dashboard looked wrong.

It stops cancelling at boundaries:

Month ends. Orders from 00:00 to 05:30 on the first of the month were counted in the previous month. That is the 1.4 percent the finance team found.

Campaign days. A sale starting at midnight IST had its first five and a half hours attributed to the day before, which made the campaign look weaker and the preceding day look better.

Day of week analysis. Monday's early hours counted as Sunday. Every weekday comparison was slightly contaminated, and the "Tuesdays are slow" conclusion someone had drawn from this data was partly an artifact.

That last one is the part that bothers me most. The bug did not just produce wrong numbers, it produced a wrong belief that people had acted on.

Why nobody caught it

Three reasons, and they generalise.

The numbers were plausible. A 1.4 percent error does not look like an error. If the dashboard had shown negative orders or a 10x spike, somebody would have investigated on day one. Wrong-but-plausible is much more dangerous than obviously broken.

Everything agreed with itself. The dashboard, the exported CSV, and the weekly email all used the same query. Three sources confirming each other feels like verification and is not, because they shared the bug.

Local development did not reproduce it. Developers ran Postgres locally on machines set to IST, so date_trunc did the right thing on their machines. The bug only existed in the environment where TZ was unset, which is exactly the environment nobody looks at.

That third one is a specific and common trap. The same shape appears in React hydration mismatches, where a server in UTC and a browser in IST disagree, and the developer's machine matches the browser so it never shows up.

Finding it

Once we knew the totals were off, the diagnosis was quick, because the error had a shape.

The discrepancy was consistently in the same direction and roughly proportional to overnight order volume. That immediately suggests a boundary problem rather than a filtering or joining problem.

The test that confirmed it, in about a minute:

SET TIME ZONE 'UTC';
SELECT date_trunc('day', created_at) d, count(*) FROM orders
WHERE created_at BETWEEN '2026-04-30' AND '2026-05-02' GROUP BY 1;

SET TIME ZONE 'Asia/Kolkata';
SELECT date_trunc('day', created_at) d, count(*) FROM orders
WHERE created_at BETWEEN '2026-04-30' AND '2026-05-02' GROUP BY 1;

Two different answers from the same data. That is the whole bug, visible in one comparison.

The fix

Be explicit about the timezone in the query. Do not depend on the session.

SELECT date_trunc('day', created_at AT TIME ZONE 'Asia/Kolkata') AS day,
       count(*), sum(total_paise)
FROM orders
WHERE created_at >= now() - interval '90 days'
GROUP BY 1 ORDER BY 1;

AT TIME ZONE on a timestamptz converts it to a timestamp in that zone, and then truncation happens in the right frame.

Set the session timezone explicitly at connection time as a backstop, so a query that forgets is still correct:

DATABASE_URL=postgres://...?options=-c%20timezone%3DAsia/Kolkata

Store the business day where it matters. For reporting that must be stable, a generated column removes the ambiguity entirely:

ALTER TABLE orders ADD COLUMN business_day date
  GENERATED ALWAYS AS ((created_at AT TIME ZONE 'Asia/Kolkata')::date) STORED;
CREATE INDEX ON orders (business_day);

Now the day is a fact recorded once, not a computation repeated in every query with the chance of being done differently each time. It is also indexable, which made the reports faster as a side effect.

Add an assertion. After the backfill, a check that the daily sums reconcile against the payment provider within a tolerance, run nightly, alerting on drift. The original bug produced no error and no alert, which is the absence-of-signal failure mode that monitoring usually misses.

What I would tell myself

Set TZ explicitly everywhere. Containers, CI runners, database sessions, application runtimes. Never rely on the default, because the default differs between your laptop and production and that difference is invisible.

I now treat an unset TZ in a Dockerfile the same way I treat an unpinned base image version.

Run one CI machine in a non-UTC timezone. If your entire fleet is UTC and your users are not, an entire class of bug is undetectable in testing. Setting TZ=Asia/Kolkata on one runner costs nothing and would have caught this.

Reconcile against an external source. Every number that matters should be checkable against something you did not compute. The payment provider's total is ground truth for revenue. Internal consistency between three views of the same query is not verification.

Be suspicious of plausible. The bugs that survive longest are the ones that produce believable output. When a number is roughly right, nobody investigates, and roughly right for three months is a lot of decisions made on bad data.

When a discrepancy has a consistent direction and size, it is structural. Random errors look random. This one was always in the same direction and always proportional to overnight volume, which pointed at a boundary long before we understood the mechanism. The same reasoning applied to a batch job that only failed on Tuesdays: the shape of the error tells you what kind of bug it is.

The backfill conversation

We recomputed three months of daily reports. Eleven days had material differences, mostly month boundaries and campaign starts.

The awkward part was not the recomputation. It was that a decision had been made about campaign timing based on the contaminated day of week data, and unwinding "we changed our promotional schedule because of a bug" is a harder conversation than "the numbers were slightly off".

That is the real cost of quietly wrong data. Not the wrong numbers, the decisions built on them.