IoT Data: Time Series and Streams

What IoT data looks like

Almost everything an IoT device sends is a time series: a sequence of values of one quantity, each with a timestamp. One P1 telegram from Lotte Kortenhoeve's smart meter (see Smart Meters and the P1 Port) contains about twenty of them: energy registers, current power, voltage, gas. A record in storage usually has four parts:

• Which device: home h0017.
• When: 7 October 2026, 17:30:15 UTC.
• What: power delivered.
• Value: 1234 W.

The values come in two kinds, and confusing them causes most calculation errors with meter data. The same distinction exists in monitoring as counters and gauges:

• Cumulative registers only go up: the energy register 1-0:1.8.2 reads 467.905 kWh, a little more each second. The energy used in a period is the difference between two readings. Averaging register values means nothing.
• Instantaneous values go up and down: current power, voltage, temperature. They are averaged, and their minimum and maximum matter.

The volumes add up quickly. Lotte's P1 reader receives a telegram every second: 86,400 a day from one home. The Kestrel Home pilot dongles send a 10-second average instead, which is still 8,640 messages per dongle per day, or 2,160,000 a day for 250 dongles. For comparison, about 400,000 smart meters with a reading every 15 minutes would produce 38.4 million rows a day (see the worked example in Math for IT).

Event time and ingestion time

Every reading has at least two moments:

• Event time is when the measurement was taken, according to the device or meter that took it. It is the moment the value describes.
• Ingestion time is when the reading arrived in the system that stores it. It says something about the pipeline, not about the house.

Most of the time they are a second apart and nobody notices. They drift apart when it matters most: when Wi-Fi drops, a broker restarts or a dongle buffers an hour of data and sends it at once. A report built on ingestion time then shows a gap followed by a spike that never happened in the house. Keep both, and calculate with event time.

Event time is only as good as the clock that produced it. The smart meter has its own clock and stamps every telegram with it, in local time plus a letter: S for summer time (UTC+2) and W for winter time (UTC+1). The letter is what makes the stamp unambiguous on the night the clocks go back:

from datetime import datetime, timedelta, timezone


def dsmr_time(text):
    """'261007193015S' -> aware datetime; S = summer time (UTC+2), W = winter (UTC+1)."""
    local = datetime.strptime(text[:12], "%y%m%d%H%M%S")
    offset = {"S": 2, "W": 1}[text[12]]
    return local.replace(tzinfo=timezone(timedelta(hours=offset)))


for stamp in ["261007193015S", "261025023000S", "261025023000W"]:
    moment = dsmr_time(stamp)
    utc = moment.astimezone(timezone.utc)
    print(f"{stamp}  {moment.isoformat()}  =  {utc:%Y-%m-%d %H:%M:%S} UTC")

261007193015S  2026-10-07T19:30:15+02:00  =  2026-10-07 17:30:15 UTC
261025023000S  2026-10-25T02:30:00+02:00  =  2026-10-25 00:30:00 UTC
261025023000W  2026-10-25T02:30:00+01:00  =  2026-10-25 01:30:00 UTC

On Sunday 25 October 2026, 02:30 happens twice in the Netherlands, an hour apart. Converted to UTC the two are different moments. Store timestamps in UTC, or with their offset, and convert to local time only for reports and for deciding which day a reading belongs to; the Time zones and daylight saving time section of Data Pipelines shows why such a day has 100 quarter-hours instead of 96.

Devices without a meter's clock, such as an ESP32, have no idea what time it is after a restart. They must get the time with NTP before they timestamp anything, or let the gateway stamp the data on arrival, which turns ingestion time into an estimate of event time. Either way, write down which one you did.

Sampling and aggregation

Hardly anyone needs a value per second for long. A dashboard needs the last few minutes; a bill needs energy per hour or per quarter hour; a year chart needs days. So raw readings are aggregated: grouped by period and summarized. For instantaneous values the summary is the mean, minimum and maximum (the mean alone hides a kettle that ran for three minutes); for registers it is the difference between the start and the end of the period.

Tumbling windows

A tumbling window is a fixed-size, non-overlapping time interval used to group a stream: 17:30–17:45, 17:45–18:00 and so on, so that every reading belongs to exactly one window. (Sliding windows, by contrast, overlap: "the last 15 minutes", recomputed every minute.) The window a reading belongs to follows from its event time: round the timestamp down to the start of the quarter hour.

Half an hour of Lotte's evening, one reading per second, with a kettle and later the oven (invented values, starting from the real register value of her meter's telegram):

from datetime import datetime, timedelta, timezone

START = datetime(2026, 10, 7, 17, 30, tzinfo=timezone.utc)   # 19:30 in Utrecht
WINDOW = 900                                                  # seconds: 15 minutes
REGISTER_START_WH = 467_905.0                                 # 1-0:1.8.2 = 467.905 kWh


def watts_at(second):
    """Invented household load: 420 W, a kettle, then the oven."""
    if 360 <= second < 540:
        return 2420              # kettle on for 3 minutes
    if 1200 <= second < 1800:
        return 2170              # oven on from 19:50
    return 420


# One reading per second for half an hour: (event time, power, energy register)
readings, register_wh = [], REGISTER_START_WH
for second in range(1800):
    watts = watts_at(second)
    register_wh += watts / 3600                               # energy used this second
    readings.append((START + timedelta(seconds=second), watts, register_wh))

# Tumbling windows by EVENT time: floor each timestamp to its quarter hour
windows = {}
for moment, watts, register in readings:
    start = datetime.fromtimestamp(moment.timestamp() // WINDOW * WINDOW, timezone.utc)
    windows.setdefault(start, []).append((watts, register))

previous_end = REGISTER_START_WH
print(f"{'window (UTC)':<12} {'n':>4} {'mean W':>7} {'min W':>6} {'max W':>6} {'kWh':>6}")
for start, rows in sorted(windows.items()):
    power = [watts for watts, _ in rows]
    energy_kwh = (rows[-1][1] - previous_end) / 1000          # register difference
    previous_end = rows[-1][1]
    label = f"{start:%H:%M}-{start + timedelta(seconds=WINDOW):%H:%M}"
    print(f"{label:<12} {len(rows):>4} {sum(power) / len(power):>7.1f} "
          f"{min(power):>6} {max(power):>6} {energy_kwh:>6.3f}")

window (UTC)    n  mean W  min W  max W    kWh
17:30-17:45   900   820.0    420   2420  0.205
17:45-18:00   900  1586.7    420   2170  0.397

1,800 readings become two rows, and nothing important is lost: the mean times a quarter hour gives the energy (820 W × 0.25 h = 0.205 kWh), the maximum shows the peak a battery or a fuse has to handle, and n shows the window is complete (900 of 900 seconds). Keep that count: a window with 412 readings is not the same quality as one with 900, even if its mean looks normal.

Late and out-of-order data

Late-arriving data is data that reaches the system after the period it belongs to has already been processed, because a device was offline, a buffer was flushed or a message was retried. Related, and just as normal, is out-of-order data: a reading from 17:44:59 that arrives after one from 17:45:00. In IoT this is not an exception to handle; it is Tuesday.

A stream processor cannot wait forever before it reports a window, so it uses a rule: wait a grace period after the window ends (stream processors often call the point up to which they consider data complete a watermark), publish the result, and recompute the window if something older still arrives. With a grace period of two minutes:

from datetime import datetime, timedelta

WINDOW = timedelta(minutes=15)
GRACE = timedelta(minutes=2)       # wait this long after a window ends


def window_start(moment):
    return moment.replace(minute=moment.minute // 15 * 15, second=0)


def t(clock):                      # all times UTC on 7 October 2026
    return datetime.strptime("2026-10-07 " + clock, "%Y-%m-%d %H:%M:%S")


# 10-second averages from pilot homes: (home, event time, arrival time)
messages = [
    ("h0017", t("17:44:50"), t("17:44:51")),
    ("h0017", t("17:44:59"), t("17:46:10")),   # Wi-Fi hiccup: one minute late
    ("h0042", t("17:44:30"), t("17:52:03")),   # dongle was offline for 8 minutes
    ("h0017", t("17:45:00"), t("17:46:10")),
]

print("home   event     arrived   window by event  by arrival  result")
for home, event, arrival in messages:
    by_event = window_start(event)
    by_arrival = window_start(arrival)
    closes = by_event + WINDOW + GRACE
    if arrival <= closes:
        verdict = "on time" if arrival < by_event + WINDOW else "within grace"
    else:
        verdict = "late: recompute"
    print(f"{home}  {event:%H:%M:%S}  {arrival:%H:%M:%S}  {by_event:%H:%M}"
          f"            {by_arrival:%H:%M}       {verdict}")

home   event     arrived   window by event  by arrival  result
h0017  17:44:50  17:44:51  17:30            17:30       on time
h0017  17:44:59  17:46:10  17:30            17:45       within grace
h0042  17:44:30  17:52:03  17:30            17:45       late: recompute
h0017  17:45:00  17:46:10  17:45            17:45       on time

Bucketing by arrival would have put two of the four readings in the wrong window. The h0042 reading arrives after the 17:30 window was published, so that window is computed again and its result replaced, not added to. Two things make that safe:

• Idempotent writes. Store readings under a key of device plus event time. A message delivered twice, which MQTT's "at least once" quality of service allows (see MQTT and IoT Messaging), then overwrites itself instead of counting double; this is the idempotent consumer pattern.
• Recompute whole windows. Rebuild the aggregate from the stored readings rather than patching a total. Batch pipelines face the same problem one level up; see incremental loads, where a meter that was offline for three days sends them all at once.

How long to wait is a trade-off between freshness and completeness. Live power in the Kestrel app is shown after seconds and may be slightly incomplete; the quarter-hour values that battery advice is based on wait two minutes; anything that feeds a bill waits for the meter's own validated data via the grid operator, which can take days.

Gaps and missing data

Devices go offline, and then there is simply no data. A missing value must stay missing in storage: None or NULL, never 0. A 0 W reading means "this house used nothing", which is a claim; a gap means "we don't know".

Gap filling is replacing missing values in a time series with estimates, for example by carrying the last value forward or by interpolating between the values on both sides, so that later calculations get a complete series. Whether it is acceptable depends on the kind of value:

from decimal import Decimal

# One home, 7 October 2026 (UTC). The dongle was offline from just after
# 18:00 until 18:45: no readings at all in between. Missing is None, not 0.
times = ["17:30", "17:45", "18:00", "18:15", "18:30", "18:45", "19:00"]
register = [Decimal("467.905"), Decimal("468.110"), Decimal("468.507"),
            None, None, Decimal("469.300"), Decimal("469.409")]   # kWh, cumulative
mean_watts = [820, 1587, None, None, None, 436]  # average power per quarter-hour

# Power is a gauge: never invent values. Statistics use what was measured.
measured = [w for w in mean_watts if w is not None]
print(f"mean of {len(measured)} measured quarter-hours: {sum(measured) / len(measured):.0f} W")
print(f"wrong, gaps filled with 0: {sum(w or 0 for w in mean_watts) / len(mean_watts):.0f} W")
print()

# Energy: the register is cumulative, so the total over a gap is exact.
# How it was spread over the gap is unknown: share it out, and flag it.
print("from   energy kWh  quality")
known = 0                                    # index of the last known register
for i in range(1, len(times)):
    if register[i] is None:
        continue
    steps = i - known                        # quarter-hours since that value
    share = (register[i] - register[known]) / steps
    for j in range(known, i):
        quality = "measured" if steps == 1 else "estimated"
        print(f"{times[j]}  {share:10.3f}  {quality}")
    known = i

mean of 3 measured quarter-hours: 948 W
wrong, gaps filled with 0: 474 W

from   energy kWh  quality
17:30       0.205  measured
17:45       0.397  measured
18:00       0.264  estimated
18:15       0.264  estimated
18:30       0.264  estimated
18:45       0.109  measured

Filling gaps with zero halves the average power. The register, on the other hand, recovers the gap: whatever happened between 18:00 and 18:45, the house used exactly 0.793 kWh in those three quarter hours, because the meter kept counting while the dongle was offline. Only the split over the three quarter-hours is a guess, which is why each row carries a quality flag. This is one of the best reasons to send cumulative registers and not only power.

Time-series databases

A time-series database (TSDB) is a database designed for timestamped measurements: it appends data quickly in time order, compresses long runs of similar values, queries by time range, and can aggregate into time buckets and delete old data cheaply, usually by dropping whole blocks of time instead of individual rows. General-purpose databases can store time series too; a TSDB makes the common operations fast and cheap at large volumes.

Two design rules apply whichever you choose. First, decide what identifies a series. In most TSDBs a small set of descriptive values (home, quantity) is indexed, and every distinct combination is a separate series; 250 pilot homes is fine, but an EAN code or a customer number per meter for 400,000 meters creates the same cardinality problem as in monitoring, and puts personal data in an index. Second, key every reading on device plus event time, so that duplicates overwrite instead of piling up.

Both rules work in plain SQL. This runs on SQLite; the primary key turns a duplicate MQTT delivery into a no-op, and integer division of Unix time by 900 seconds finds the quarter hour:

-- 10-second averages from two pilot homes; ts = event time, Unix seconds (UTC)
CREATE TABLE power (
  home  TEXT    NOT NULL,
  ts    INTEGER NOT NULL,
  watts INTEGER NOT NULL,
  PRIMARY KEY (home, ts)            -- one value per home per moment
);

INSERT INTO power VALUES
  ('h0017', 1791394200,  430),      -- 17:30:00 UTC
  ('h0017', 1791394560, 2410),      -- 17:36:00, kettle
  ('h0017', 1791394800, 2420),      -- 17:40:00
  ('h0017', 1791395100,  420),      -- 17:45:00
  ('h0017', 1791395700, 2170),      -- 17:55:00, oven
  ('h0042', 1791394200,  310),
  ('h0042', 1791395110,  290);

-- The same message delivered a second time: ignored, not counted twice
INSERT OR IGNORE INTO power VALUES ('h0017', 1791394560, 2410);

SELECT home,
       datetime(ts / 900 * 900, 'unixepoch') AS quarter_hour,
       count(*) AS n,
       round(avg(watts)) AS mean_w,
       max(watts) AS max_w
FROM power
GROUP BY home, ts / 900
ORDER BY home, quarter_hour;

home   quarter_hour         n  mean_w  max_w
-----  -------------------  -  ------  -----
h0017  2026-10-07 17:30:00  3  1753.0  2420
h0017  2026-10-07 17:45:00  2  1295.0  2170
h0042  2026-10-07 17:30:00  1  310.0   310
h0042  2026-10-07 17:45:00  1  290.0   290

A TSDB offers the same thing as a function, for example time_bucket('15 minutes', ts) in TimescaleDB or date_bin in the SQL of InfluxDB 3, and can keep such aggregates up to date automatically. At home, Lotte's Home Assistant keeps its history in a local database on the Raspberry Pi and builds long-term statistics from it, which is enough for one house (see Smart Home and Building Automation).

Retention and downsampling

Raw data is most useful in its first hours and most expensive, in storage and in privacy risk, in its later years. Downsampling is replacing fine-grained data with coarser aggregates as it gets older: 1-second readings become 15-minute windows, 15-minute windows become days. A retention policy for time series says how long data at each resolution is kept before it is downsampled further or deleted, and the database enforces it automatically. It is the time-series form of the data retention policy an organization has for all its data.

For the pilot, DBA Hamid Rahimi proposed these periods, to be approved by Femke Jansen after advice from the data protection officer. The 13 months match Kestrel's existing rule for the quarter-hour readings of consenting customers, enough to compare a month with the same month a year earlier. Lotte's own system is hers to decide: data that never leaves her home falls under her own judgment, not under Kestrel's policy.

Three rules keep a retention policy honest:

• Downsample before you delete. The job that builds quarter-hour aggregates must have finished, and checked its counts, before the raw data it read from expires.
• Choose aggregates for the questions you will ask. A daily mean cannot tell you the evening peak; keep the maximum too if peaks matter, as they do for batteries and grid congestion.
• Delete in the copies as well. Backups, exports and test databases follow the same policy, or the policy only exists on paper (see storage limitation).

Data quality at the source

A wrong value is cheapest to catch where it is produced. The P1 telegram already carries a CRC that the reader checks before it trusts a single number (see Smart Meters and the P1 Port). After that, a few plausibility checks on the device or the gateway catch most of what remains:

from datetime import datetime

LOW_V, HIGH_V = 207.0, 253.0       # 230 V plus or minus 10%


def checks(previous, reading, received):
    """Return the problems with one reading, given the one before it."""
    problems = []
    if reading["time"] > received:
        problems.append("time in the future")
    if previous and reading["time"] <= previous["time"]:
        problems.append("not newer than previous")
    if previous and reading["kwh"] < previous["kwh"]:
        problems.append("register went down")
    if not LOW_V <= reading["volts"] <= HIGH_V:
        problems.append(f"voltage {reading['volts']} V out of range")
    return problems


def t(clock):
    return datetime.fromisoformat("2026-10-07T" + clock + "+00:00")


received = t("17:31:00")           # when this batch reached the gateway
batch = [
    {"time": t("17:30:15"), "kwh": 467.905, "volts": 231.4},
    {"time": t("17:30:16"), "kwh": 467.905, "volts": 231.2},
    {"time": t("17:30:16"), "kwh": 467.905, "volts": 231.2},   # duplicate
    {"time": t("17:30:17"), "kwh": 46.791, "volts": 231.3},    # digits lost
    {"time": t("17:30:18"), "kwh": 467.906, "volts": 23.1},    # decimal slipped
    {"time": t("19:30:19"), "kwh": 467.906, "volts": 231.1},   # local time as UTC
]

previous = None
for reading in batch:
    problems = checks(previous, reading, received)
    print(f"{reading['time']:%H:%M:%S}  {'; '.join(problems) or 'ok'}")
    if not problems:
        previous = reading         # compare with the last GOOD reading

17:30:15  ok
17:30:16  ok
17:30:16  not newer than previous
17:30:17  register went down
17:30:18  voltage 23.1 V out of range
19:30:19  time in the future

The voltage range comes from the European supply-quality standard EN 50160, which expects 230 V plus or minus 10% for 95% of the 10-minute averages in a week under normal conditions. A single one-second value outside that range is not yet a breach of the standard, but it is either a real grid event worth knowing about or a parsing error. Notes on the other checks:

• A register that goes down is almost always an error, with one legitimate exception: the grid operator replaced the meter, and the new one starts near zero. Flag it for a person; never "fix" it automatically.
• A time two hours in the future is the classic sign of local summer time stored as if it were UTC.
• Compare with the last good value, not the last value, so one bad reading does not make the next good one look wrong.
• Flag, don't drop silently. Store rejected readings with the reason, or at least count them per device. A dongle that rejects 30% of its telegrams has a loose cable, and that is worth a call (see data quality).