Field notes — the blog

Notes from inside the system.

Specific write-ups on decisions that moved the needle: architecture trade-offs, delivery pipelines, and leading engineers who are better than you at their part of it.

              {{ totalPosts }} notes
              {{ totalMinutes }} min total
              Monthly
            

/

                Latest
                {{ featured.date }}
                {{ featured.cat }}
                {{ featured.read }}
              

{{ featured.title }}

{{ featured.excerpt }}

Read the note →

// before → after
{{ row.text }}

{{ listHeading }}

{{ resultCount }}

                {{ post.cat }}
                {{ post.date }}
              

{{ post.title }}

{{ post.excerpt }}

{{ post.read }}

Nothing matches that.

Try a different term, or clear the filters.

The architect's dispatch

One note a month. Nothing else.

A single deep write-up on a system I worked on: the trade-off, the numbers, what I'd do differently.

            {{ article.cat }}
            {{ article.date }}
            {{ article.read }}
          

{{ article.title }}

{{ article.excerpt }}

{{ part.text }}{{ part.text }}{{ part.text }}

{{ b.alt }} — {{ b.caption }}

{{ b.label }}

{{ h.text }}

{{ cell.text }}

{{ b.label }}

{{ link.label }} {{ link.note }} {{ link.host }}

{{ b.caption }}

{{ b.text }}

{{ b.text }}

{{ b.label }}

{{ line.text }}

{{ item.no }}

{{ part.text }}

{{ part.text }}

{{ part.text }}

Takeaways

▖ {{ t }}

Tags

{{ tag }}

← Newer

{{ prevPost.title }}

Older →

{{ nextPost.title }}

Related notes

{{ post.cat }}

{{ post.title }}

{{ post.read }}

Two tables for the same number: what a timezone did to our rollups

Architecture · Aug 2026 · 19 min read

A daily total is not a fact about the data. It is a fact about whose calendar you asked in — and once you have summed the rows, you can never ask again.

Our activity product stores one row per tracked interval: a member id, a start instant, an end instant, both UTC. Hundreds of millions of them. Nothing reads that table. Every screen, every export, every alert reads a daily rollup — one row per member per day, with tracked, active and idle seconds already summed. It is the single reason the dashboards answer in 40ms instead of nine seconds, and for about a year it was the least interesting part of the system.

Then we onboarded a customer whose team is in Los Angeles, and their operations lead sent a screenshot with a one-line question: why does everyone here work three hours before they arrive?

The bug is one line of the rollup job

The rollup keyed its bucket off the UTC calendar, because that is what the timestamps were in and nobody had a reason to think about it.

// One bucket per member per day.
const day = interval.startedAt
  .toISOString()
  .slice(0, 10);            // '2026-07-14'

buckets[day] = (buckets[day] ?? 0) + seconds(interval);

Los Angeles is UTC−7 in July, so the UTC day rolls over at 17:00 local. Every hour a west-coast employee works after five in the afternoon lands on tomorrow's row. It is not an edge case that fires occasionally — it fires for every single person, every single day.

A ten-hour working day in Los Angeles shown against both calendars. On the local timeline, work runs 09:00 to 12:00, 13:00 to 18:00, and 20:30 to 22:30. A dashed red line marks 17:00 local, which is midnight UTC. On the UTC timeline the same blocks are split by that line: seven hours land on the Tuesday row and three hours on the Wednesday row. — Ten hours worked on Tuesday, stored as seven and three. Neither number misstates the seconds; both misstate the day.

Note what the screenshot actually showed. Not missing time — the weekly total was exactly right. Time on the wrong day, in both directions, consistently. That is a worse failure than losing data, because every aggregate above it still balances and nothing looks broken until somebody who knows their own schedule reads a row.

The fix that does not exist

My first instinct was the cheap one: leave the rollup alone, and shift it at read time. The tenant is UTC−7, so subtract seven hours somewhere in the query and hand back the corrected day. I got about twenty minutes into writing it before the shape of the problem became obvious.

SELECT day, SUM(trackedSeconds) AS tracked
FROM dailyActivity
WHERE memberId = ? AND day BETWEEN ? AND ?
GROUP BY day;

-- ...then relabel each day by the tenant's offset.
-- Which relabels the row. It does not move the
-- seconds, because there is nothing left to move.

A row holding trackedSeconds = 25200 does not record which seconds. Summing is lossy in exactly the dimension a different day boundary needs. You can slice an interval anywhere you like; you cannot slice a number.

Two strips. On the top, raw intervals sit under a day boundary that slides; the shaded region grows and shrinks and the intervals are re-cut wherever the boundary lands. On the bottom the same day has been summed into a single row holding 25,200 seconds; the boundary slides across it and nothing changes, because the number has no interior to cut. — The top strip can answer any calendar. The bottom strip can answer exactly one, and it already has.

This is the whole post, really. Everything after it is consequence. A rollup is not a cache of the raw data — a cache can be recomputed into a different shape from what it holds. A rollup is a *projection*, and a projection commits you to the question you projected along.

A daily total is not a property of the events. It is a property of the calendar you chose before you added them up.

So aggregate on read, then

The correct-by-construction option is to stop rolling up and compute from the intervals per request, with the tenant's zone applied to each one. I tried it against production-shaped data to find out what it cost.

one member, one month, on the same hardware
Source	p50	p95
dailyActivity rollup	38ms	61ms
raw intervals, aggregated per request	8.9s	31s

That is not an indexing problem I was going to fix. A team dashboard fans this out across forty members and a date range, and the interval table grows by roughly a million rows a day. The rollup exists for a reason and it was going to keep existing. Which left one honest conclusion: if a rollup can only answer one calendar, and we need two calendars, we need two rollups.

Two tables, and why keeping both is the point

The obvious move is to change the existing rollup to bucket by the tenant's zone and be done. We deliberately did not. We added a second table and kept the first.

The worker reads the intervals once and writes both. They are not a duplicate of each other — they answer different questions.

dailyActivity stayed as the ledger. It is the only aggregate in the system that means the same thing to every tenant, which makes it the one you want for billing periods, for a fleet-wide window, for comparing two customers who are nine hours apart. dailyActivityTz is what a human is shown. Its numbers are correct for a named calendar and meaningless outside it.

The schema, and the column people leave out

CREATE TABLE dailyActivityTz (
  memberId  BIGINT UNSIGNED NOT NULL,
  tenantId  BIGINT UNSIGNED NOT NULL,
  -- The IANA name, never an offset. '-07:00' is a
  -- fact about one instant; 'America/Los_Angeles'
  -- is a fact about a calendar.
  tz        VARCHAR(64) NOT NULL,
  day       DATE NOT NULL,   -- local day in `tz`
  -- 82800 | 86400 | 90000. The denominator belongs
  -- with the bucket, not in the reader.
  daySeconds     MEDIUMINT UNSIGNED NOT NULL,
  trackedSeconds INT UNSIGNED NOT NULL DEFAULT 0,
  activeSeconds  INT UNSIGNED NOT NULL DEFAULT 0,
  idleSeconds    INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (memberId, tz, day),
  KEY tenant_window (tenantId, tz, day)
);

Two of those lines are the ones I would argue for hardest.

Storing the IANA name rather than an offset is not pedantry. An offset is the answer to "what was the difference at this moment", and it changes twice a year — store -07:00 and your winter rows are silently an hour out. The name is the answer to "which set of rules", which is the thing that is actually stable about a tenant.

daySeconds is the column nobody puts in and everybody eventually needs. Any percentage over a day needs a denominator, and readers who derive it themselves will write 86400. Storing it with the bucket means the one component that knows about DST — the rollup worker — is the only component that has to.

Twice a year the bucket changes size. Nine tracked hours is 39.1% of one of these days and 36.0% of another.

The 25-hour day has a second effect that took us longer to find. We have an alert for implausible tracking — more than twenty hours in a day usually means a stuck agent. On the November Sunday, a genuinely long shift can clear that bar honestly. The alert now compares against daySeconds, not a constant.

Whose timezone, though

"Convert to local time" hides a question with three plausible answers, and they disagree.

the three candidates
Calendar	Answers	Why we did not use it
The member's own	"Did this person work nights?"	Changes when they travel; a report would shift retroactively
The viewer's browser	"What is today for me?"	Two managers export the same team and get different numbers
The tenant's reporting tz	"What is a working day here?"	This one. It is a deliberate setting, not an inference

The viewer's zone is the tempting one because the browser hands it to you for free, and it is the worst of the three. A report that changes because somebody opened it on a plane is not a report. We pinned a reportingTz column on the tenant, defaulted it from the billing address at signup, and put it on the settings screen where somebody accountable can change it.

The member's own zone did not disappear — it is a genuine second fact, and it is what the night-shift and coverage views need. It just is not the reporting calendar, and conflating them is how you end up with a team total that does not equal the sum of its members.

Splitting, not assigning

The rollup worker's job changes in one important way: an interval that crosses a bucket edge belongs to both buckets. The instinct is to assign it to the day it started in. Do that and a night shift reports twelve hours on Monday and zero on Tuesday.

// An interval can span any number of buckets.
// Split it; never assign it.
function* slice(interval, zone) {
  let cursor = interval.startedAt;
  while (cursor < interval.endedAt) {
    const start = DateTime.fromJSDate(cursor, { zone })
      .startOf('day');
    // plus({ days: 1 }) re-resolves the calendar.
    // + 86400000 does not, and is wrong twice a year.
    const edge = start.plus({ days: 1 }).toJSDate();
    const until = edge < interval.endedAt
      ? edge : interval.endedAt;
    yield {
      day:        start.toISODate(),
      daySeconds: edge - start.toJSDate(),   // ms
      seconds:    (until - cursor) / 1000,
    };
    cursor = until;
  }
}

The single line worth staring at is the edge. plus({ days: 1 }) asks the calendar what the next midnight is; adding 86,400,000 milliseconds asks arithmetic. They agree 363 days a year, which is precisely why the disagreement ships.

Recompute the bucket, never increment it

Intervals arrive late. An agent goes offline in a building with no signal and flushes four hours of history the next morning, and that history belongs to yesterday's buckets, which were finalised overnight.

The tempting write is trackedSeconds = trackedSeconds + ?. It is wrong for a reason that has nothing to do with timezones: it is not idempotent, so a retried job double-counts, and every queue you will ever use is at-least-once.

-- Recompute the whole bucket from the intervals that
-- intersect it, then write the total. Running this
-- twice is the same as running it once.
INSERT INTO dailyActivityTz
  (memberId, tenantId, tz, day, daySeconds,
   trackedSeconds, activeSeconds, idleSeconds)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
  daySeconds     = VALUES(daySeconds),
  trackedSeconds = VALUES(trackedSeconds),
  activeSeconds  = VALUES(activeSeconds),
  idleSeconds    = VALUES(idleSeconds);

The dirty-bucket set is what makes this affordable. A late interval marks (memberId, tz, day) dirty for every bucket it touches, in every materialised calendar, and the worker drains that set. It does not rescan a day because a day passed.

Do not materialise every timezone

There are about 340 usable IANA zones. Materialising a member-day in each is a 340× write amplification for a table that is already the largest thing you write, and it buys nothing — nobody is going to read the Kathmandu row of a customer in Ohio.

Materialise the calendars somebody actually reads, which the tenants table already tells you.

-- In practice this returns single digits, and most
-- rows share a handful of values.
SELECT DISTINCT reportingTz
FROM tenants
WHERE deletedAt IS NULL;

Scoped per member — you only need the zones of the tenants that member belongs to — this comes out at 1.0 to 1.2 rows per member-day for us. The 340× version is a table nobody can afford; this one is barely larger than the original.

The test that would have caught it

Our reconciliation check passed the entire time the bug was live, because it summed a month and a bucketing error does not change a monthly total. The fix is to state the invariant per bucket, and to use a zone whose offset is not zero.

// 1. Per bucket, not per window. A month-wide sum is
//    blind to seconds moving between days.
for (const day of interiorDays(window)) {
  expect(rollup(day, zone))
    .toBe(sumIntervalsWithin(day, zone));
}

// 2. Fixtures in UTC prove nothing. Every case runs
//    against a zone with a real offset and real DST.
test.each([
  'America/Los_Angeles',  // -7/-8, DST
  'Asia/Karachi',         // +5, no DST
  'Australia/Lord_Howe',  // +10:30, 30-min DST
])('buckets correctly in %s', ...);

interiorDays drops the first and last day of the window, because those buckets are genuinely partial and asserting on them tests the harness rather than the code. And Lord Howe Island is in there on purpose: its DST shift is thirty minutes, so any code that assumes transitions are whole hours fails there and nowhere else.

What I would do again

Keeping the UTC table. It was the decision I was least sure about at the time — it looked like carrying a duplicate for sentimental reasons — and it is the one that has paid off most, because every question about an absolute window still has a cheap answer.

And putting the calendar in the key. The thing I actually got wrong was not the UTC bucketing; it was writing a rollup whose bucketing rule lived in the code that produced it rather than in the row it produced. A row that says which calendar it belongs to can be joined by others, backfilled alongside, and audited. A row that does not is a number whose meaning you have to go and look up, and by the time anyone looks it up they are already debugging.

Takeaways

A daily total is a projection along one calendar. Summing destroys exactly the information a different calendar needs, so read-time conversion is not a fix.
A bucketing bug moves seconds between buckets without changing any wide total — reconciliation over a month will pass throughout.
Keep the UTC rollup even when nothing reads it: it is the only aggregate that means the same thing to every tenant.
Store the IANA zone name, not an offset, and put it in the primary key so a tenant changing timezone is an insert rather than a rewrite.
Store daySeconds with the bucket. A day is 82,800 or 90,000 seconds twice a year, and that breaks percentages and threshold alerts.
Split intervals at every bucket edge in a loop; do not assign them to the day they started, and do not special-case a single crossing.
Recompute buckets rather than incrementing them — at-least-once delivery makes += a double-count waiting to happen.
Test per bucket, in a zone with a real offset. Fixtures in UTC prove nothing at all.

All notes · Shehzad Aslam