It was not a timezone bug, it was a day-boundary bug

TL;DR#

"Store everything in UTC" is correct advice and solves half the problem. An instant stored in UTC is unambiguous. A day is not an instant — and every revenue report, daily-active-user count, streak, and "orders today" query converts instants into days using some zone.

Ours used three different ones: the database session's zone for SQL reports, the server's for a nightly job, and the browser's for the dashboard. Three numbers for "yesterday's revenue", all computed from correctly-stored UTC data, differing by up to 4.1%.

Declare the reporting zone explicitly, once, and pass it everywhere a day boundary is computed. The default is always someone's accident.

The case this post is about#

Timestamps stored properly:

CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    total_minor BIGINT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()    -- correct: instant with offset
);

And three consumers, each bucketing by day:

-- 1. the SQL report, run from a BI tool
SELECT created_at::date AS day, sum(total_minor)
FROM orders GROUP BY 1 ORDER BY 1 DESC LIMIT 30;

// 2. the nightly job
val yesterday = LocalDate.now().minusDays(1)          // the SERVER's zone
val from = yesterday.atStartOfDay().toInstant(ZoneOffset.UTC)

// 3. the dashboard
const day = new Date(order.createdAt).toLocaleDateString()   // the BROWSER's zone

Three implementations, three zones, none of them stated anywhere.

The symptom#

Three different answers for the same day:

"revenue for 2026-11-12"
  BI tool (SQL):          £184,201
  nightly job email:      £176,940     (−3.9%)
  dashboard:              £183,118     (−0.6%)

The finance team reconciling against the payment provider:

payment provider's settlement report (Europe/London days):  £184,201
our nightly job:                                            £176,940
discrepancy:                                                  £7,261

Seven thousand pounds that did not exist — the same orders, assigned to different days.

And the cause was mundane once we looked:

-- the BI tool's connection
SHOW timezone;                  -- Europe/London
-- the application's connection pool
SHOW timezone;                  -- UTC
-- the nightly job's container
$ date +%Z                      -- UTC
-- the finance team's browser
                                -- Europe/London (GMT, and BST half the year)

created_at::date in Postgres converts the timestamptz to the session's zone before taking the date. Same query, same data, different session, different answer.

The worst instance was a DST night:

2026-10-25, the night BST → GMT (clocks go back; 25-hour local day)
  orders between 01:00 and 02:00 local occurred TWICE
  UTC-bucketed:      assigned to one 24-hour window, 1 hour of orders landed on the 26th
  London-bucketed:   correctly 25 hours of orders on the 25th
  difference:        £4,890 (4.1%) on that one day

Why the obvious fix failed#

"Store everything in UTC." Already done, correctly. The data was never the problem — the bucketing was, and UTC storage does nothing to make three consumers agree about where a day starts.

"Set the database session timezone to UTC everywhere." This makes all three agree, on the wrong answer. A UK business's "Tuesday's revenue" means Tuesday in London, not Tuesday in UTC — and for seven months of the year those differ by an hour.

"Use the user's timezone." Right for a user-facing "your orders today" and wrong for a company revenue report, which must be one number that reconciles with the payment provider. Different questions need different zones, which is the actual insight and not a single setting.

"Convert in the application and stop doing date maths in SQL." Helps, and it moved the implicit zone from the database session to the JVM's default — which is also ambient. We did this and the numbers changed again without becoming right.

The actual mechanism#

Two different things, and the advice conflates them:

An instant is a point on the timeline. 2026-11-12T23:30:00Z is unambiguous. Storing it as TIMESTAMPTZ is correct, and ordering and subtracting instants needs no zone.

A day is a half-open interval of instants, and which interval depends on a zone:

Those two rows for Europe/London are the whole bug in miniature, and they are why it survived review. In November the UTC and London intervals coincide exactly — so every test written in winter passes, every manual check agrees, and the code is correct for roughly half the year. The defect has a season.

Which also explains why "store everything in UTC" felt like it had already solved this. It had — for the instant half of the problem, completely and correctly. What it does not touch is the boundary question, because converting a zone-dependent interval into UTC does not make the interval zone-independent; it just moves where the zone was applied, and applying it at zero is still applying one.

The reliable tell is a date appearing anywhere near an instant. A column typed DATE, a toLocalDate(), a truncate('day'), a GROUP BY on a formatted string — each is a point where a timeline has been cut into intervals, and each needs a zone supplied deliberately rather than inherited from whatever the server happens to be set to.

"2026-11-12" in UTC              = [2026-11-12T00:00Z, 2026-11-13T00:00Z)
"2026-11-12" in Europe/London    = [2026-11-12T00:00Z, 2026-11-13T00:00Z)   (GMT: same)
"2026-07-12" in Europe/London    = [2026-07-11T23:00Z, 2026-07-12T23:00Z)   (BST: shifted)
"2026-11-12" in America/New_York = [2026-11-12T05:00Z, 2026-11-13T05:00Z)

So any operation that produces a day from an instant — ::date, toLocalDate(), truncate('day'), toLocaleDateString() — takes a zone, and if you did not pass one it used an ambient default: the session's, the process's, the browser's.

That ambient default is the bug. Three consumers, three ambient defaults, and nothing in the code revealing that a choice was being made.

Instants need no zone. Days do, and every day-bucketing operation takes one whether or not you pass it. Not passing one means inheriting an ambient default that differs per consumer.

The fix#

One declared reporting zone, passed explicitly at every boundary computation.

1. Declare it, once, where everyone can see it:

// commonMain — the company's reporting zone. NOT the server's, NOT the user's.
// Chosen to match the payment provider's settlement day so finance reconciles.
val REPORTING_ZONE: TimeZone = TimeZone.of("Europe/London")

2. SQL takes the zone explicitly:

-- AT TIME ZONE converts to the named zone before truncating. No session dependency.
SELECT (created_at AT TIME ZONE 'Europe/London')::date AS day,
       sum(total_minor) AS revenue_minor
FROM orders
GROUP BY 1 ORDER BY 1 DESC LIMIT 30;

Run from any session, any client, same answer.

3. The job computes the window as instants, from the declared zone:

// the day is resolved to an instant RANGE once, then only instants are compared
fun dayWindow(day: LocalDate, zone: TimeZone = REPORTING_ZONE): ClosedRange<Instant> {
    val start = day.atStartOfDayIn(zone)
    val end = day.plus(1, DateTimeUnit.DAY).atStartOfDayIn(zone)
    return start..end
}

val window = dayWindow(Clock.System.now().toLocalDateTime(REPORTING_ZONE).date.minus(1, DAY))
val revenue = orders.sumBetween(window.start, window.endInclusive)   // >= start, < end

atStartOfDayIn is the function that handles the DST night correctly — on a spring-forward day midnight may not exist, and on a fall-back day the 25-hour interval is produced properly.

4. The dashboard is given the day, not an instant to bucket:

// the API returns pre-bucketed days computed in the reporting zone.
// the client formats for DISPLAY and never recomputes a boundary.
type DailyRevenue = { day: string /* ISO date in the reporting zone */, revenueMinor: number }

This is the structural half of the fix: the client cannot disagree about a boundary it does not compute.

5. And the zone is labelled in every output, so a reader can tell which day is meant:

Daily revenue — days are Europe/London
2026-11-12    £184,201

That line resolved three months of arguments with finance by itself.

Plus a lint rule, because ambient defaults come back:

# CI: flag zone-less day extraction
grep -rnE '::date|toLocalDate\(\)|truncate\(.day.|toLocaleDateString\(\)' --include='*.kt' \
     --include='*.sql' --include='*.ts' . \
  | grep -v 'AT TIME ZONE\|REPORTING_ZONE\|zone =' \
  && { echo "❌ day extraction without an explicit zone"; exit 1; }

How we proved it#

The three consumers, reconciled:

"revenue for 2026-11-12"
                        before      after
BI tool (SQL)         £184,201   £184,201
nightly job           £176,940   £184,201
dashboard             £183,118   £184,201
payment provider      £184,201   £184,201

Four numbers, one answer, and it matches the settlement report — which is the only external check that mattered.

The DST day, which is the case a naive fix gets wrong:

2026-10-25 (25-hour local day)
  UTC bucketing:              £114,210
  AT TIME ZONE 'Europe/London': £119,100
  payment provider:            £119,100

And the spring-forward day, the other direction:

2026-03-29 (23-hour local day)
  UTC bucketing:              £98,440
  AT TIME ZONE 'Europe/London': £94,120
  payment provider:            £94,120

Both directions correct, which is the thing atStartOfDayIn and AT TIME ZONE buy over any manual offset arithmetic.

A regression test on the boundaries themselves:

@Test
fun dayWindowsHandleDstTransitions() {
    val zone = TimeZone.of("Europe/London")

    // fall back: a 25-hour day
    val oct25 = dayWindow(LocalDate(2026, 10, 25), zone)
    assertEquals(25.hours, oct25.endInclusive - oct25.start)

    // spring forward: a 23-hour day
    val mar29 = dayWindow(LocalDate(2026, 3, 29), zone)
    assertEquals(23.hours, mar29.endInclusive - mar29.start)

    // and no gaps or overlaps between consecutive days
    val a = dayWindow(LocalDate(2026, 10, 25), zone)
    val b = dayWindow(LocalDate(2026, 10, 26), zone)
    assertEquals(a.endInclusive, b.start)
}

manual 24-hour arithmetic:  FAILED — expected 25h but was 24h
atStartOfDayIn:             PASSED

The last assertion — consecutive days meeting exactly — is the one that catches an off-by-an-hour that loses or double-counts an hour of orders.

The audit for the same pattern elsewhere:

day-bucketing sites found:              31
with an explicit zone (before):          2
using an ambient default:               29
  of those, user-facing (user's zone correct):   11
  of those, reporting (needed REPORTING_ZONE):   18

Eighteen reporting sites inheriting a default, of which three had visible discrepancies anyone had noticed.

One limit: REPORTING_ZONE is a single company-wide zone, which works because this business settles in one. A multi-region business needs a zone per reporting entity, and then "revenue today" is not one number — which is a modelling decision this fix does not make for you.

What this generalizes to#

"Store it in UTC" is about storage and says nothing about aggregation. The advice is correct and incomplete, and the incompleteness is where the money goes. Every bucketing, grouping, truncation or "today" comparison needs a declared zone.

An ambient default is a decision nobody made. Session timezone, process locale, default charset, browser language, working directory. Each one makes code behave differently by environment, and the code gives no sign that a choice exists.

Resolve calendar concepts to instant ranges once, then compare instants. Instants are total and unambiguous. Doing the conversion at one boundary and comparing instants everywhere else removes the zone from the rest of the system.

Label the zone in the output. "Days are Europe/London" under a revenue table costs one line and answers the question every reader of a daily report eventually asks.

Related#

• kotlinx-datetime has no Calendar, and the timezone database ships per platform — the client-side version of this
• Offset pagination loses rows while you read — another silently-wrong aggregate
• Engagement went up, and it was because the app got slower — numbers that disagree with reality

Keep reading#

This is part of 100 practices of senior engineers — the habits that separate five years of experience from one year five times. Follow MobileByteSensei for the rest.