Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 36 ยท Edge, Cloud and Data Pipelines

Time-Series Storage, Queries and Dashboards

Store sensor readings as time series, query them with time buckets in SQL, downsample with retention, spot anomalies with a rolling average and draw a text chart.

IoT data has a very particular shape: an endless stream of small records, each tagged with a time, almost always appended and rarely edited. You ask questions like "average temperature per hour last week" or "when did the pressure first exceed 6 bar?". Databases built for that shape are called time-series databases (InfluxDB, TimescaleDB, Prometheus, QuestDB, AWS Timestream), though a plain SQL table with a good index works well at small scale.

This lesson shows how to model the data, query it by time bucket, shrink it over time and put it on a dashboard. It continues from the data pipeline that produced the clean readings.

The data model

A time-series point has four parts:

  • a measurement name (temperature),
  • tags that identify the series and are indexed (device=s1, room=kitchen),
  • one or more fields holding the values (celsius=21.4),
  • a timestamp.

Tags have few distinct values; fields vary. Putting a high-cardinality value (a request ID, a raw timestamp) in a tag makes the number of series explode and slows the database. As a relational table the same thing looks like this:

SQL
CREATE TABLE readings (
  device TEXT NOT NULL,
  ts INTEGER NOT NULL,     -- Unix seconds, UTC
  celsius REAL NOT NULL,
  PRIMARY KEY (device, ts)
);

The primary key on (device, ts) also gives you free deduplication: inserting the same reading twice can be made harmless with INSERT OR IGNORE.

Querying by time bucket

The fundamental query groups by a time bucket. In SQL you divide the timestamp: ts / 3600 gives the hour number. The example uses Node's built-in SQLite engine (in memory, so nothing is saved).

Node.js Hourly averages with SQL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Time-series databases wrap this in friendlier syntax, such as InfluxQL's GROUP BY time(1h) or TimescaleDB's time_bucket('1 hour', ts) (read-only here):

SQL
SELECT time_bucket('1 hour', ts) AS hour, device, avg(celsius)
FROM readings
WHERE ts > now() - interval '7 days'
GROUP BY hour, device
ORDER BY hour;

Retention and downsampling

Per-second data for five years is rarely worth its cost. Typical policy:

Resolution Kept for
Raw (every second) 7 days
1-minute averages 90 days
1-hour min/avg/max 5 years

A background job downsamples: it computes the coarser rows, stores them, then deletes the raw rows older than the limit. Always store min and max with the average, as in the pipeline lesson.

Detecting anomalies with a rolling window

Dashboards are more useful when they highlight surprises. A simple, explainable method: compare each value with the mean and standard deviation of the previous N readings (a rolling z-score). A value more than three standard deviations away is flagged.

Node.js Rolling z-score anomaly detection

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Only the spike at index 11 is reported. The rolling window adapts to slow trends, so normal drift is not flagged. Beware cold starts (not enough history) and legitimate regime changes such as a machine switching to a heavier load.

A text dashboard

A real dashboard (Grafana, a web page with a chart library) queries the same buckets and draws them. The panels of a good IoT dashboard are: a current value with a status color, a trend line for a recent window, a min/max band, an alert list, and a device health panel (last seen time, battery, signal). The program below draws a bar chart in text from hourly averages, enough to see the idea.

Node.js A bar chart from bucketed data

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Tips

  • Store timestamps in UTC and convert only for display.
  • Choose the dashboard refresh rate to match the data rate; refreshing every second a value that changes hourly wastes resources.
  • Show staleness: a flat line can mean a stable value or a dead sensor. Display "last updated 14 min ago".
  • Alerts should have a duration ("above 23 for 10 minutes") to avoid noise.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which is the best candidate for a tag (indexed identifier) in a time-series model?

    Tags should have few distinct values and identify the series. Values and unique IDs belong in fields or nowhere.
  2. 2. What does downsampling do?

    It keeps long-term history cheaply: min, avg and max per larger bucket.
  3. 3. Why does a primary key on (device, ts) help?

    The same reading inserted twice violates the key and can be ignored.
  4. 4. In a rolling z-score detector, what does the window do?

    Comparing with recent history adapts the baseline to gradual change.

Summary

  • Time-series points have a measurement, tags, fields and a timestamp; keep tag cardinality low.
  • Group by time buckets for hourly or daily views; a key on (device, ts) gives free deduplication.
  • Apply retention and downsampling; keep min/avg/max.
  • A rolling z-score is a simple anomaly detector; show staleness and use alert durations.

Next: IoT Threat Modeling: What Can Go Wrong