Time-Series Storage & Interval Modeling for Municipal Meter Data

Once a meter read has passed validation, it still is not billable — it has to be turned into a record that answers a precise question: how much energy did this meter consume between this UTC instant and that one, and can you prove it later? This page sits inside Meter Data Ingestion & Validation Pipelines, and it addresses the modeling problem that decides whether a billing engine can trust its own history. A raw AMI feed delivers cumulative register snapshots — lifetime odometer totals — but a bill is computed from intervals, the energy consumed inside a bounded window. Get the interval model wrong and every downstream figure inherits the flaw: a period total that double-counts a re-delivered read, a correction that silently overwrites the number an auditor already saw, a daylight-saving day billed as if it had 24 hours when it had 23 or 25. The four invariants this page enforces are: every interval is anchored to timezone-aware UTC instants, every stored energy value is a Decimal derived from cumulative registers, every record is immutable and corrections append a new version, and every period total is reproducible from the stored series alone.

# Prerequisites

This modeling work assumes the reads reaching it are already typed and bounded. Interval storage is a layer above ingestion, not a replacement for it.

• Python 3.11+. Required for zoneinfo, datetime.fromisoformat round-tripping of offset-aware instants, and modern dataclass slots semantics. Never pytz; never naive datetime.
• decimal.Decimal for every register value and every derived interval energy. Interval energy is a difference of two registers; binary float subtraction introduces drift that reconciliation against the register endpoints will later reject.
• zoneinfo for the utility’s civil time zone. Reads are stored in UTC and rendered in civil time; the civil zone decides which billing day a late-night interval belongs to.
• Validated input. Reads arriving here have already cleared the schema validation and data quality gate — types, ranges, and timestamp awareness are guaranteed before an interval is ever derived.
• A per-read idempotency identity carried from AMI/AMR feed synchronization, so a re-delivered register snapshot resolves to the same interval rather than a duplicate.
• Append-only (write-once) storage for the interval series and its corrections, the same chain-of-custody discipline PUC and GASB reviewers expect of the ledger it feeds.
• Data-schema assumption: meters report cumulative registers. Interval energy is always a register delta, which is what makes a replayed feed converge to zero net change instead of inflating totals.

# Architecture Overview

The model has two halves that must stay consistent: the shape of a single canonical interval record, and the layout that stores millions of them so a billing period can be summed cheaply. A cumulative snapshot enters, the pipeline derives the energy consumed since the prior snapshot, and the resulting immutable interval is written into a store partitioned by meter and by time. Corrections never mutate a stored row — they append a higher version, and the highest version is the one a period aggregation reads.

Figure: A cumulative snapshot becomes an immutable interval record, written into a store partitioned by meter and month; corrections append a version rather than overwriting.

The two design choices that carry the billing weight are visible in the diagram. First, energy is derived, never transported — the record stores both register endpoints and the delta, so any interval can be re-checked against its own registers without trusting the arithmetic that produced it. Second, the store is append-only and partitioned by time, so sealing a billed month is a metadata operation and summing a period never scans a meter’s entire lifetime history. Everything that follows implements these two decisions.

# Step-by-Step Implementation

# Step 1 — Define the canonical interval record as a frozen, self-validating structure

The record is the contract the rest of the system depends on, so it is immutable and it refuses to exist in an inconsistent state. A frozen dataclass gives value semantics (two records with identical fields are equal, which makes replay comparisons trivial) and rejects post-construction mutation, which is what keeps a “correction” from becoming a silent in-place edit. The energy is derived through a factory so a record can never disagree with its own registers.

from __future__ import annotations

from dataclasses import dataclass, replace
from datetime import datetime
from decimal import Decimal


@dataclass(frozen=True, slots=True)
class IntervalRead:
    """One immutable, billable interval derived from two cumulative register reads."""
    meter_id: str
    interval_start_utc: datetime      # inclusive, timezone-aware (UTC)
    interval_end_utc: datetime        # exclusive, timezone-aware (UTC)
    register_start: Decimal           # cumulative register at interval_start
    register_end: Decimal             # cumulative register at interval_end
    interval_kwh: Decimal             # derived energy = register_end - register_start
    version: int = 1                  # 1 = original; corrections append higher versions
    source: str = "AMI"
    quality: str = "ACTUAL"           # ACTUAL | ESTIMATED | CORRECTED

    def __post_init__(self) -> None:
        if self.interval_start_utc.tzinfo is None or self.interval_end_utc.tzinfo is None:
            raise ValueError("interval bounds must be timezone-aware (stored as UTC)")
        if self.interval_end_utc <= self.interval_start_utc:
            raise ValueError("interval_end_utc must be strictly after interval_start_utc")
        # The stored energy must reconcile to its own registers — no float, no rounding here.
        if self.interval_kwh != self.register_end - self.register_start:
            raise ValueError("interval_kwh must equal register_end - register_start")

    @classmethod
    def from_registers(
        cls,
        meter_id: str,
        start: datetime,
        end: datetime,
        register_start: Decimal,
        register_end: Decimal,
        **kw: object,
    ) -> "IntervalRead":
        """Build a record whose energy is derived, so it cannot contradict its registers."""
        return cls(
            meter_id=meter_id,
            interval_start_utc=start,
            interval_end_utc=end,
            register_start=register_start,
            register_end=register_end,
            interval_kwh=register_end - register_start,
            **kw,
        )

The invariant checks are cheap and they run at the exact moment a bad record would otherwise enter storage. A naive timestamp, a zero-length or inverted interval, or an energy value that does not equal its register delta are all structurally impossible to persist — which means every later query can assume they never happen.

# Step 2 — Store cumulative registers and derive contiguous interval deltas

Meters report the odometer, not the trip. The pipeline sorts the incoming snapshots by UTC instant and pairs each read with its predecessor, so the energy for the window is the register difference. Storing both the cumulative endpoints and the derived delta is deliberate: the delta is what the rate engine bills, and the cumulative endpoints are what reconciliation and audit re-derive it from. A backwards delta is never stored as consumption — it is a rollover or a meter change, and it is routed to anomaly handling rather than laundered into a plausible interval.

from decimal import Decimal


def build_intervals(
    reads: list[tuple[datetime, Decimal]],
    meter_id: str,
    source: str = "AMI",
) -> list[IntervalRead]:
    """Turn ordered cumulative register snapshots into contiguous interval records."""
    ordered = sorted(reads, key=lambda r: r[0])           # anchor everything to UTC order
    intervals: list[IntervalRead] = []
    for (t0, reg0), (t1, reg1) in zip(ordered, ordered[1:]):
        delta = reg1 - reg0
        if delta < 0:
            # Register moved backwards: rollover or meter replacement, not consumption.
            # Do NOT store a delta here — hand it to anomaly detection instead.
            continue
        intervals.append(
            IntervalRead.from_registers(
                meter_id, t0, t1, reg0, reg1, source=source
            )
        )
    return intervals

Because the derivation depends only on sorted register content, re-running it on a re-delivered feed produces byte-identical intervals — the property that makes the whole series safe to rebuild. Backwards deltas are handed to the reading anomaly detection algorithms layer, which distinguishes a genuine rollover from a corrupt read before either is allowed to affect a bill.

# Step 3 — Version corrections append-only, never overwrite

A correction is not an edit. When a re-read, an estimate replacement, or a PUC-ordered adjustment changes an interval that was already stored, the system appends a new version of that interval and leaves the prior version in place. The store is keyed on (meter_id, interval_start_utc), and each key holds an ordered history; the highest version is the current truth, and every superseded version stays readable for audit. dataclasses.replace produces the new version from the old one without ever mutating a frozen record.

from collections import defaultdict


class IntervalStore:
    """Append-only interval store: corrections add a version; nothing is overwritten."""

    def __init__(self) -> None:
        # key = (meter_id, interval_start_utc) -> version history, oldest first
        self._history: dict[tuple[str, datetime], list[IntervalRead]] = defaultdict(list)

    def append(self, rec: IntervalRead) -> None:
        """Store an interval. Re-storing an identical current version is a no-op."""
        history = self._history[(rec.meter_id, rec.interval_start_utc)]
        if history and history[-1] == rec:
            return                                        # idempotent replay: no new version
        history.append(rec)

    def correct(
        self,
        meter_id: str,
        interval_start_utc: datetime,
        corrected_register_end: Decimal,
    ) -> IntervalRead:
        """Supersede the current version with a corrected one (append, never mutate)."""
        history = self._history[(meter_id, interval_start_utc)]
        if not history:
            raise KeyError("cannot correct an interval that was never stored")
        prior = history[-1]
        corrected = replace(
            prior,
            register_end=corrected_register_end,
            interval_kwh=corrected_register_end - prior.register_start,
            version=prior.version + 1,
            quality="CORRECTED",
        )
        history.append(corrected)
        return corrected

    def current(self, meter_id: str, interval_start_utc: datetime) -> IntervalRead:
        """The highest version is the current, billable truth for this interval."""
        return self._history[(meter_id, interval_start_utc)][-1]

The append guard makes storage itself idempotent: replaying a feed that has not changed adds no versions, so a nightly re-sync cannot inflate the history. A real correction, by contrast, always increments the version and stamps CORRECTED, giving an auditor a complete lineage from the original read to the value that finally billed. This is the same append-only discipline used across audit logging and data governance.

# Step 4 — Aggregate a billing period from current versions only

A period total is a sum over the current version of every interval whose start falls inside the half-open window [period_start, period_end). Using the half-open convention means an interval that starts exactly at the cycle boundary belongs to exactly one period — never zero, never two. Summing only current versions means a corrected interval contributes its corrected energy without any need to delete or rewrite the superseded rows.

from decimal import Decimal


def period_total_kwh(
    store: IntervalStore,
    meter_id: str,
    period_start_utc: datetime,
    period_end_utc: datetime,
) -> Decimal:
    """Sum current-version interval energy for one meter over a half-open UTC window."""
    total = Decimal("0")
    for (m_id, start), history in store._history.items():
        if m_id != meter_id:
            continue
        if period_start_utc <= start < period_end_utc:    # half-open: [start, end)
            total += history[-1].interval_kwh             # current version only
    return total

Because the window is expressed in UTC instants, a billing cycle that spans a daylight-saving transition needs no special arithmetic here — the civil-time cycle boundary is resolved to a UTC instant once, and the sum is boundary-agnostic. Rendering that same period back to a customer in civil time is a presentation concern handled at the edge, not in the aggregation. This total is the quantity the step-rate and block-rate structures then price.

# Edge-Case Handling

The interval model earns its keep on the awkward days, not the clean ones. Each of the following has produced wrong municipal bills when the storage layer ignored it.

• DST-length intervals. A civil “day” is 23 or 25 hours twice a year, and a nominal daily interval derived from wall-clock arithmetic will be short or long by an hour. Because intervals are anchored to UTC instants and energy is a register delta, the duration varies correctly and the energy is still exact — but any code that assumes a fixed interval length for gap detection must compute the expected count from zoneinfo, not from a constant. The boundary arithmetic itself is covered in computing seasonal rate boundaries across DST transitions.
• Gaps and missing intervals. A dropped read leaves a hole in the series. Do not silently bridge it with a larger delta across the gap, because that attributes the missing window’s consumption to whichever interval happens to span it. Detect the gap by comparing expected against stored interval starts, mark the window explicitly, and estimate it as a flagged ESTIMATED record so the hole is visible to audit rather than absorbed. When no estimate is possible, control passes to fallback routing for missing rate data.
• Late-arriving and backfilled reads. A snapshot that arrives after its neighbors were already stored must slot into the correct UTC position, which can split an interval that was previously derived across the now-filled gap. Re-derive the affected window from registers and store the results as new versions; never append the late read to the tail of the series where its timestamp does not belong.
• Register rollover in storage. A fixed-width register that wraps to zero produces a large negative delta. Step 2 refuses to store this as consumption; the true energy across a genuine rollover is (register_max - register_start) + register_end, computed only after anomaly detection confirms a rollover rather than a corrupt read. Storing the raw negative delta would post a huge negative correction to a customer’s bill.
• Retroactive corrections after a period was billed. A PUC order or a re-read can change an interval in a cycle that already invoiced. Because storage is append-only and versioned, the correction appends a new version and a re-rate of the affected period produces an additive adjustment record — the original bill and the corrected total both remain provable, which is exactly what a reconciliation and audit review requires.

# Verification and Audit Trail

An interval series is only trustworthy if it can prove two things: that its energy reconciles to the registers it was derived from, and that replaying the feed changes nothing. The first check is a conservation law — for a contiguous, gap-free window, the sum of interval energies must equal the difference between the last and first cumulative registers, net of any documented rollover. The second is replay idempotence — re-ingesting a batch that has not changed must add zero versions and leave every current value untouched. Both run against historical fixtures before any change to the storage layer ships, the same discipline the error handling and retry workflows apply to transient failures.

from decimal import Decimal


def reconciles_to_registers(intervals: list[IntervalRead]) -> bool:
    """Conservation check: summed interval energy must equal the register endpoints' delta."""
    if not intervals:
        return True
    ordered = sorted(intervals, key=lambda r: r.interval_start_utc)
    summed = sum((r.interval_kwh for r in ordered), Decimal("0"))
    endpoint_delta = ordered[-1].register_end - ordered[0].register_start
    return summed == endpoint_delta


def replay_is_idempotent(store: IntervalStore, batch: list[IntervalRead]) -> bool:
    """Re-appending an unchanged batch must create no new versions."""
    before = {k: len(v) for k, v in store._history.items()}
    for rec in batch:
        store.append(rec)                                  # append() no-ops identical records
    after = {k: len(v) for k, v in store._history.items()}
    return before == after

A failing conservation check means an interval was stored with an energy value that no longer matches its registers — usually a hand-edited row that bypassed the frozen record, exactly the mutation the model exists to prevent. A failing idempotence check means __eq__ is not stable across a round trip, which almost always traces to a float sneaking in where a Decimal belongs, or a timestamp that is not normalized to the same UTC representation on every ingest.

# Troubleshooting

# Frequently Asked Questions

# Should I store cumulative registers, interval deltas, or both?

Store both. The cumulative registers are the source of truth a meter actually reports and the value reconciliation re-derives energy from; the interval delta is the quantity the rate engine bills. Keeping both in the same record lets any interval be re-checked against its own endpoints without trusting the arithmetic that produced it, and it makes the conservation check — summed deltas equal the endpoint difference — a one-line assertion.

# Why store everything in UTC instead of the utility’s local time?

UTC is monotonic and unambiguous; civil time is neither. Storing interval instants in UTC means a daylight-saving transition never produces a duplicated or skipped local hour in the series, and period arithmetic works on a clean number line. The civil zone still matters — it decides which billing day an interval belongs to — but that is a rendering and boundary-resolution concern applied at the edge, not a reason to persist local wall-clock times.

# How do I correct an interval without destroying the audit trail?

Append a new version rather than editing the stored row. Each (meter_id, interval_start_utc) key holds an ordered version history; a correction produces a higher version stamped CORRECTED, and the original stays readable forever. Period aggregation reads only the highest version, so the corrected energy bills while the full lineage — original read, every revision, final value — remains provable for a PUC or GASB reviewer.

# How should the store be partitioned for fast period aggregation?

Partition by a meter bucket and by calendar time, typically month. A billing period for one meter then touches at most one or two partitions instead of scanning the meter’s entire lifetime history, and sealing a fully billed month becomes a metadata operation. The partition key should be derived deterministically from meter_id and the interval’s UTC month so the same interval always lands in the same partition on every ingest.

# What interval length should I store across a DST change?

Store the interval exactly as bounded by its UTC instants — the duration will naturally be 23 or 25 hours for a nominal daily interval on the transition day, and that is correct. What must not use a fixed length is gap detection: compute the expected number of intervals for the civil day from zoneinfo so a legitimately short or long DST day is not flagged as missing or duplicated reads.

# Related Topics

• Storing Interval Reads in a Time-Series Schema — the focused table schema and idempotent upsert that persists the records modeled here.
• Schema Validation & Data Quality Checks — the boundary gate that guarantees reads are typed and bounded before an interval is derived.
• AMI/AMR Feed Synchronization Protocols — where the per-read idempotency identity that makes replay safe originates.
• Async Batch Processing for High-Volume Reads — the throughput layer that derives and stores millions of intervals per cycle.
• Reading Anomaly Detection Algorithms — the layer that classifies backwards deltas as rollover or corruption before they reach storage.

Up one level: Meter Data Ingestion & Validation Pipelines · Return to the utilitybilling.org home.