Somewhere in your plant there is a spreadsheet that multiplies a column by 24. Everyone who touches it knows the incantation; almost nobody left on site can explain it. This post is about that column, why the multiplication exists, the days on which it is quietly wrong, and why the fix is an archive design problem rather than a spreadsheet problem.
The per-day totaliser wound
The pattern is decades old. A flow computer or PLC maintains a totaliser — an accumulating count of cubic metres, kilograms, kilowatt-hours. The historian samples it. And because the incumbent tooling made daily reports easy and accumulating counters hard, somebody at commissioning configured the totaliser to reset at midnight, or configured the historian to log a "daily total" tag derived from it.
From that day on, the archive does not contain what the meter measured. It contains a sawtooth, plus an assortment of derived tags whose derivation lives in one retired engineer's head.
The ×24 ritual
Then the reporting requirement changes — head office wants monthly totals, or totals aligned to shift boundaries rather than midnight. The archive has daily resets, so someone exports the "average flow" tag instead and multiplies by 24 to recover a daily quantity, because the flow is in units per hour and there are 24 hours in a day.
Except sometimes the tag is in units per minute, so the multiplier is 1440. Sometimes it is per second, and it is 86,400. The spreadsheet does not say which; it says =B2*24 and the units live in tribal memory. Every plant we have visited has at least one report where two of these conventions are mixed and the totals are plausible enough that nobody has noticed.
And then there are the days when even the right multiplier is wrong:
- On the spring DST changeover the day is 23 hours long. Average flow × 24 overstates the day's production by roughly 4.3%. Autumn understates it. Twice a year, silently, forever.
- During a collector outage the "daily average" is the average of the hours the historian happened to see. If the line was down for the missing hours, the ritual invents production; if the line was running, it deletes some. Either way the number carries no marker that anything happened.
- On the day the totaliser rolls over — a 32-bit count wrapping at 4,294,967,296, or a flow computer wrapping at 1,000,000 — the delta goes hugely negative and the daily total becomes nonsense, unless a second spreadsheet incantation catches it. Sometimes it does.
None of these failures announce themselves. That is the defining property of the whole arrangement: it is wrong in ways that produce plausible numbers.
How counters should be archived
The principle is the one that runs through everything else we build: archive what the instrument said, and do the arithmetic at query time, where it can be repeated, corrected and audited.
For a totaliser that means:
- Store the raw monotonic count. Not a daily reset, not a derived rate, not a pre-computed total. The count, timestamped, as sampled.
- Declare the rollover. The wrap point is a property of the instrument, so it belongs on the series definition — not in a spreadsheet formula guarding cell B2.
- Compute totals as rollover-aware deltas at query time. A total over any window — a day, a shift, a lunar month — is the delta of the count across that window, corrected for however many wraps occurred inside it.
- Attach provenance. If the collector was down for 40 minutes of the window, the result must say so, because a totaliser delta across a gap is still exact — the count kept counting — but a rate integrated across a gap is not, and the query engine should know the difference.
What OrdinateDB does
OrdinateDB has a counter series kind for exactly this. The rollover is declared once, on the series:
series line2/FQI-201 {
kind: counter
units: m3
rollover: 1000000 # from the flow computer's datasheet
}
Totals are a query-time operation over the raw counts, windowed however the question demands:
SELECT window_start, total, pct_good, rollovers
FROM windowed_total('line2/FQI-201',
'2026-02-01', '2026-03-01',
window => '1d',
tz => 'Europe/London');
The tz argument matters: a "day" in a plant is a civil day, and the engine builds 23- and 25-hour windows across DST changes instead of pretending every day has 24 hours. rollovers reports how many wraps were unwound inside each window. And pct_good, as everywhere else in the system, confesses how much of the window the archive actually observed — the number never travels without it. The mechanics are in SPEC 04, and the storage side of the story is on the archive page.
Because the raw count is what is stored, the same archive answers the shift-aligned question, the monthly question and next year's question nobody has asked yet, without re-instrumenting anything. If you are carrying daily-reset tags over from a legacy historian, the migration guide covers how to map them onto counter series without losing the history you already have.
The fix is not a smarter spreadsheet
It is tempting to patch the ritual — add a DST lookup table, a rollover guard, a units cell. Every plant that has tried now has a smarter spreadsheet that is wrong in smarter ways, and still only in the one workbook that got patched.
The multiply-by-24 column exists because an archive threw away the count and kept a summary. Store the count, declare the rollover, integrate at query time, and the column — along with its twice-yearly 4.3% lie — simply has nothing left to do.