A meter reads 1,050 kWh at 09:00 and 1,200 kWh at 10:00. How much energy was consumed between those readings, and when was it consumed? The first answer is 150 kWh. The second is more interesting than it looks. Get it wrong and your hourly chart shifts an hour to the right, and sooner or later someone will wonder why their data's an hour off.
Reading isn't writing
It helps to keep a clear separation in your mind between the data that's persisted and the data that's returned from a query. Unless you specifically query the persisted history, what you get back has likely been through a transformation pipeline. One such example is folding and interpolation; others include unit conversion, data quality, and shifting timestamps. Holding that separation in your head is what makes time series weirdness debuggable when it shows up.
What gets persisted is rarely what comes back. The read pipeline reshapes it on the way out.
All of those transformations rest on a single rule about timestamps: each one marks the start of a period. A reading at 10:00 with a five-minute interval is the value valid from 10:00 (inclusive) up to 10:05 (exclusive). Different databases have different conventions (some put the timestamp at the start of the period, others at the end), but this article assumes start-of-period throughout. Every other rule below is downstream of that one.
For an instantaneous reading like a temperature probe sampled every five minutes, that semantics fits naturally. A value of 15°C at 10:00 means the probe read 15°C from 10:00 until 10:05. Folding three of those into a fifteen-minute average gives you the average temperature over the window. No tricks.
Totalised consumption
Energy meters work differently. Most are totalised: they store a continuously incrementing reading rather than a per-period delta. The database turns that into consumption by computing the difference between consecutive readings, typically at read time. Sum the raw values and you'd get an absurd number; sum the deltas and you get total consumption.
The complexity is in the timestamp. Going back to our example at the top: 1,050 kWh at 09:00 and 1,200 kWh at 10:00. The delta of 150 kWh is the energy consumed between those two readings. The natural place to attach it is at the timestamp where the difference was computed, 10:00. But the start-of-period rule says a value at 10:00 represents the period starting at 10:00, not the one ending there. Left alone, the consumption shows up an hour late.
The fix is for the read pipeline to shift each delta back by one period before returning it. The 150 kWh now lives at 09:00, where it belongs. This shift is usually opt-in, controlled by a flag on the point that marks the history as consumption rather than instantaneous readings.
The same five raw readings, viewed through each layer of the read pipeline. Tap a stage to reshape the chart and highlight its column.
What's actually persisted on disk. A continuously incrementing meter reading.
| Time | Persisted | Calculation | Delta | Shifted |
|---|---|---|---|---|
| 08:00 | 1,000 kWh | — | — | 50 kWh |
| 09:00 | 1,050 kWh | 1,050 − 1,000 | 50 kWh | 150 kWh |
| 10:00 | 1,200 kWh | 1,200 − 1,050 | 150 kWh | 100 kWh |
| 11:00 | 1,300 kWh | 1,300 − 1,200 | 100 kWh | 150 kWh |
| 12:00 | 1,450 kWh | 1,450 − 1,300 | 150 kWh | — |
Rollovers
The persisted reading only climbs. With enough time and enough use, every counter eventually runs out of room.
Analogue meters have a finite range. When the counter reaches its maximum it rolls over and starts again at zero, the way an old car odometer flips from 999,999 back to 000,000. The next read sees a value smaller than the previous one and the delta computation produces a negative number.
Digital meters inherit the same problem in a different form. The values just look weirder. The rollover happens at the limits of fixed-width integer storage rather than at decimal digit limits, typically 65,535 (16 bits) or 4,294,967,295 (32 bits). Whenever you see a history roll over at one of those values, you're looking at integer overflow rather than a digit wheel.
In a normal period the delta between two readings is the straightforward difference:
If we know the value at which the meter rolls over, we can capture it as part of the meter's configuration and treat any negative delta as a rollover rather than a fresh start. Across a rollover, the formula becomes:
That recovers the consumption that crossed the rollover point. Without it, every rollover silently under-counts by however much the meter passed through between the previous reading and its maximum. The widget below applies both rules across ten hourly readings; the calculation column shows the swap explicitly.
Ten hourly readings on a meter that rolls over at 999 kWh. The dashed line shows where the reading would be if the dial kept counting. Hover the chart or the table to inspect a single bucket.
The 999 kWh rollover is illustrative; a real meter wouldn't roll over at this magnitude.
| Time | Persisted | Calculation | Delta |
|---|---|---|---|
| 08:00 | 700 kWh | — | — |
| 09:00 | 750 kWh | 750 − 700 | 50 kWh |
| 10:00 | 810 kWh | 810 − 750 | 60 kWh |
| 11:00 | 890 kWh | 890 − 810 | 80 kWh |
| 12:00 | 950 kWh | 950 − 890 | 60 kWh |
| 13:00rollover | 30 kWh | (999 − 950) + 30 | 79 kWh |
| 14:00 | 80 kWh | 80 − 30 | 50 kWh |
| 15:00 | 140 kWh | 140 − 80 | 60 kWh |
| 16:00 | 220 kWh | 220 − 140 | 80 kWh |
| 17:00 | 310 kWh | 310 − 220 | 90 kWh |
| Total consumption | 609 kWh | ||
What we did at CoolPlanet
I also wrote an internal CoolPlanet wiki on this topic, mostly because every new engineer hit the same totalisation landmines. The most damaging of those landmines is the spike. One bad reading, an order of magnitude out, and the rest of the day's chart flattens around it.
Twenty-four hourly readings from a totalised meter, with the computed hourly consumption charted below. Inject a single bad reading at 09:00 and watch one rogue value cascade into two corrupt deltas.
The 503,200 kWh spike is illustrative; the cascade pattern is what matters, not the magnitude.
| Time | Persisted | Calculation | Delta | Shifted |
|---|---|---|---|---|
| 00:00 | 50,000 kWh | — | — | 25 kWh |
| 01:00 | 50,025 kWh | 50,025 − 50,000 | 25 kWh | 22 kWh |
| 02:00 | 50,047 kWh | 50,047 − 50,025 | 22 kWh | 20 kWh |
| 03:00 | 50,067 kWh | 50,067 − 50,047 | 20 kWh | 20 kWh |
| 04:00 | 50,087 kWh | 50,087 − 50,067 | 20 kWh | 25 kWh |
| 05:00 | 50,112 kWh | 50,112 − 50,087 | 25 kWh | 35 kWh |
| 06:00 | 50,147 kWh | 50,147 − 50,112 | 35 kWh | 50 kWh |
| 07:00 | 50,197 kWh | 50,197 − 50,147 | 50 kWh | 75 kWh |
| 08:00 | 50,272 kWh | 50,272 − 50,197 | 75 kWh | 95 kWh |
| 09:00 | 50,367 kWh | 50,367 − 50,272 | 95 kWh | 110 kWh |
| 10:00 | 50,477 kWh | 50,477 − 50,367 | 110 kWh | 120 kWh |
| 11:00 | 50,597 kWh | 50,597 − 50,477 | 120 kWh | 130 kWh |
| 12:00 | 50,727 kWh | 50,727 − 50,597 | 130 kWh | 125 kWh |
| 13:00 | 50,852 kWh | 50,852 − 50,727 | 125 kWh | 120 kWh |
| 14:00 | 50,972 kWh | 50,972 − 50,852 | 120 kWh | 115 kWh |
| 15:00 | 51,087 kWh | 51,087 − 50,972 | 115 kWh | 100 kWh |
| 16:00 | 51,187 kWh | 51,187 − 51,087 | 100 kWh | 85 kWh |
| 17:00 | 51,272 kWh | 51,272 − 51,187 | 85 kWh | 70 kWh |
| 18:00 | 51,342 kWh | 51,342 − 51,272 | 70 kWh | 60 kWh |
| 19:00 | 51,402 kWh | 51,402 − 51,342 | 60 kWh | 50 kWh |
| 20:00 | 51,452 kWh | 51,452 − 51,402 | 50 kWh | 40 kWh |
| 21:00 | 51,492 kWh | 51,492 − 51,452 | 40 kWh | 35 kWh |
| 22:00 | 51,527 kWh | 51,527 − 51,492 | 35 kWh | 30 kWh |
| 23:00 | 51,557 kWh | 51,557 − 51,527 | 30 kWh | — |
Click the button above to swap the 09:00 reading for one that's an order of magnitude out.
We weren't catching a 100 kW boiler registering 101 kW, that's just measurement. We were catching the same meter suddenly registering 10,000 kW, the kind of value that's a glitch upstream rather than a real measurement. Spikes were just the start. Over time we built up a library of patterns we'd seen in the wild: stuck readings, negative consumption, unit slips. Each had a signature, and each got a detector and either a correction or a filter applied automatically on read.
Our time-series database, Skyspark, exposes hooks between the persisted history and the read pipeline. We placed the data-quality library at those hooks. It runs on every history read, so performance had no slack: we dropped from Axon to Fantom for the hot path and painstakingly justified every line. The result is two coexisting views of the same data: raw on disk for incident debugging, cleansed for the rest of the platform.