- Decide first what a reading means between samples. For change-of-value (COV) data the value holds until the next report (a step); for regularly sampled analogue data a straight line between samples may be closer. The SQL follows from that choice.
- A plain
avg()over irregular samples weights each report equally, so bursts of reports during a change dominate. In the example, the hour's plain average is 22.075 and the time-weighted average is 20.836. - Time-weighted averages need the value in force at the start of the window, not only the readings inside it. Fetch it with a "last reading at or before" lookup.
date_binplusGROUP BYsilently omits empty buckets. Generate the grid withgenerate_seriesand join to it; then choose explicitly between NULL, last observation carried forward and interpolation.- Carry values forward only up to a staleness limit, so a dead sensor does not show its last value for days. The 20-minute limit in the example is illustrative; derive yours from each device's heartbeat or maximum reporting interval.
Why sensor data is irregular, and who this is for
Three things make building and IoT readings irregular. Devices that report on change send nothing while a value is steady and several reports in quick succession while it moves; BACnet change-of-value subscriptions work this way, with a configurable minimum change (the COV increment) for analogue values. Communication gaps leave holes of minutes or hours. And pollers drift: a "one minute" poll lands at 08:00:01, 08:01:03, 08:01:58.
This guide is for developers and analysts who must turn those readings into numbers people trust: hourly averages for energy reports, 15-minute values for a chart, a value at exactly 08:00 for a comfort KPI. It uses plain PostgreSQL. Where readings come from and how they are stored are covered in How to Store IoT Sensor Data and BACnet Readings to PostgreSQL; data that arrives late is covered in Late-Arriving and Out-of-Order Sensor Events.
| Signal | Typical source | Meaning between samples | Use |
|---|---|---|---|
| Setpoints, states, COV-reported temperatures | BACnet COV, event-driven gateways | Value holds until the next report (step) | LOCF and step time-weighting |
| Regularly polled analogue values | Polling every 1 to 60 s | Value changes continuously; samples are snapshots | Linear interpolation, or step weighting if the poll is fast |
| Counters and meter registers | kWh registers, pulse counts | Cumulative; only differences matter | Difference between readings, apportioned by time |
| Alarms and events | Alarm systems, CMMS triggers | Not a measured value | Do not average; count or measure duration |
The example: one zone temperature reported on change
One point, a zone temperature, reports on change. A brief spike after 08:02 produces three reports in under a minute, then the value sits at 21.0 for 38 minutes. The window of interest is 08:00 to 09:00.
SET TimeZone = 'UTC';
CREATE TABLE reading (
point_id integer NOT NULL,
ts timestamptz NOT NULL,
value double precision NOT NULL,
PRIMARY KEY (point_id, ts)
);
INSERT INTO reading (point_id, ts, value) VALUES
(1, '2026-10-05 07:56:00+00', 20.6),
(1, '2026-10-05 08:02:10+00', 23.5),
(1, '2026-10-05 08:02:40+00', 23.4),
(1, '2026-10-05 08:03:05+00', 21.0),
(1, '2026-10-05 08:41:20+00', 20.4),
(1, '2026-10-05 09:03:00+00', 20.9);SELECT ts::text AS ts,
(ts - lag(ts) OVER (ORDER BY ts))::text AS since_previous
FROM reading
WHERE point_id = 1
ORDER BY ts;
-- ts | since_previous
-- ------------------------+----------------
-- 2026-10-05 07:56:00+00 |
-- 2026-10-05 08:02:10+00 | 00:06:10
-- 2026-10-05 08:02:40+00 | 00:00:30
-- 2026-10-05 08:03:05+00 | 00:00:25
-- 2026-10-05 08:41:20+00 | 00:38:15
-- 2026-10-05 09:03:00+00 | 00:21:40Time-weighted average instead of avg()
The plain average treats the three spike reports as three equal votes. It also ignores the 20.6 that was in force from 07:56 until 08:02:10, because that reading is outside the window.
SELECT count(*) AS samples, round(avg(value)::numeric, 3) AS naive_avg
FROM reading
WHERE point_id = 1
AND ts >= '2026-10-05 08:00+00' AND ts < '2026-10-05 09:00+00';
-- samples | naive_avg
-- ---------+-----------
-- 4 | 22.075Build step segments, then weight by duration
Under the step interpretation each value holds from its timestamp until the next reading. The query below adds the value in force at the window start (the last reading at or before 08:00), uses lead() to find where each segment ends, and closes the last segment at the window end. Readings exactly at the window start are taken by the carry-in lookup, which is why the in-window filter uses ts > t0.
WITH win AS (
SELECT timestamptz '2026-10-05 08:00+00' AS t0,
timestamptz '2026-10-05 09:00+00' AS t1
), pts AS (
SELECT r.ts, r.value
FROM reading r, win
WHERE r.point_id = 1 AND r.ts > win.t0 AND r.ts < win.t1
UNION ALL
SELECT win.t0, (SELECT r.value FROM reading r
WHERE r.point_id = 1 AND r.ts <= win.t0
ORDER BY r.ts DESC LIMIT 1)
FROM win
), seg AS (
SELECT ts AS seg_start,
coalesce(lead(ts) OVER (ORDER BY ts), (SELECT t1 FROM win)) AS seg_end,
value
FROM pts
WHERE value IS NOT NULL
)
SELECT seg_start::text, seg_end::text, value,
extract(epoch FROM seg_end - seg_start)::int AS seconds
FROM seg ORDER BY seg_start;
-- seg_start | seg_end | value | seconds
-- ------------------------+------------------------+-------+---------
-- 2026-10-05 08:00:00+00 | 2026-10-05 08:02:10+00 | 20.6 | 130
-- 2026-10-05 08:02:10+00 | 2026-10-05 08:02:40+00 | 23.5 | 30
-- 2026-10-05 08:02:40+00 | 2026-10-05 08:03:05+00 | 23.4 | 25
-- 2026-10-05 08:03:05+00 | 2026-10-05 08:41:20+00 | 21 | 2295
-- 2026-10-05 08:41:20+00 | 2026-10-05 09:00:00+00 | 20.4 | 1120-- WITH win AS (...), pts AS (...), seg AS (...) as above
SELECT round((sum(value * extract(epoch FROM seg_end - seg_start))
/ sum(extract(epoch FROM seg_end - seg_start)))::numeric, 3) AS time_weighted_avg,
sum(extract(epoch FROM seg_end - seg_start))::int AS covered_s
FROM seg;
-- time_weighted_avg | covered_s
-- -------------------+-----------
-- 20.836 | 3600Per-bucket time-weighted averages
For a chart or a report you usually want one value per 15 minutes. Clip each segment to each bucket with greatest() and least() and weight by the overlap. The covered_s column shows how many seconds of the bucket had a known value; if it is less than the bucket length, part of the bucket had no data and you should say so rather than report the average as complete.
WITH win AS (
SELECT timestamptz '2026-10-05 08:00+00' AS t0,
timestamptz '2026-10-05 09:00+00' AS t1
), pts AS (
SELECT r.ts, r.value
FROM reading r, win
WHERE r.point_id = 1 AND r.ts > win.t0 AND r.ts < win.t1
UNION ALL
SELECT win.t0, (SELECT r.value FROM reading r
WHERE r.point_id = 1 AND r.ts <= win.t0
ORDER BY r.ts DESC LIMIT 1)
FROM win
), seg AS (
SELECT ts AS seg_start,
coalesce(lead(ts) OVER (ORDER BY ts), (SELECT t1 FROM win)) AS seg_end,
value
FROM pts
WHERE value IS NOT NULL
), buckets AS (
SELECT b AS b_start, b + interval '15 minutes' AS b_end
FROM win, generate_series(win.t0, win.t1 - interval '15 minutes', interval '15 minutes') AS b
)
SELECT b.b_start::text AS bucket,
round((sum(s.value * extract(epoch FROM least(s.seg_end, b.b_end) - greatest(s.seg_start, b.b_start)))
/ sum(extract(epoch FROM least(s.seg_end, b.b_end) - greatest(s.seg_start, b.b_start))))::numeric, 3) AS twa,
sum(extract(epoch FROM least(s.seg_end, b.b_end) - greatest(s.seg_start, b.b_start)))::int AS covered_s
FROM buckets b
JOIN seg s ON s.seg_start < b.b_end AND s.seg_end > b.b_start
GROUP BY b.b_start
ORDER BY b.b_start;
-- bucket | twa | covered_s
-- ------------------------+--------+-----------
-- 2026-10-05 08:00:00+00 | 21.092 | 900
-- 2026-10-05 08:15:00+00 | 21.000 | 900
-- 2026-10-05 08:30:00+00 | 20.853 | 900
-- 2026-10-05 08:45:00+00 | 20.400 | 900Gap filling: generate the grid, then decide what goes in it
date_bin assigns each reading to the start of its bucket, aligned to an origin you choose. Grouped on its own it returns only buckets that contain readings, so the 08:15 and 08:45 buckets simply disappear and a chart would draw a line straight across them. Generate every bucket with generate_series and left-join the aggregates to it, so empty buckets appear with zero samples and a NULL value.
WITH grid AS (
SELECT g.bucket
FROM generate_series(timestamptz '2026-10-05 08:00+00',
timestamptz '2026-10-05 08:45+00',
interval '15 minutes') AS g(bucket)
), agg AS (
SELECT date_bin(interval '15 minutes', ts, timestamptz '2000-01-01 00:00+00') AS bucket,
count(*) AS samples, avg(value) AS avg_value
FROM reading
WHERE point_id = 1
AND ts >= '2026-10-05 08:00+00' AND ts < '2026-10-05 09:00+00'
GROUP BY 1
)
SELECT grid.bucket::text AS bucket,
coalesce(agg.samples, 0) AS samples,
round(agg.avg_value::numeric, 2) AS avg_value
FROM grid LEFT JOIN agg USING (bucket)
ORDER BY grid.bucket;
-- bucket | samples | avg_value
-- ------------------------+---------+-----------
-- 2026-10-05 08:00:00+00 | 3 | 22.63
-- 2026-10-05 08:15:00+00 | 0 |
-- 2026-10-05 08:30:00+00 | 1 | 20.40
-- 2026-10-05 08:45:00+00 | 0 |Last observation carried forward, with a staleness limit
For step data the honest value at 08:30 is the last reading before 08:30, provided it is recent enough to trust. A LATERAL subquery looks up, for each grid time, the newest reading at or before it; with the (point_id, ts) primary key the planner can answer it with a short backward index scan per slot (the plan was not inspected here; check it with EXPLAIN on your data). The status column marks a slot stale when the carried value is older than a limit you set per point. The 20-minute limit here is an illustrative assumption for the example, not a recommended value; base the real limit on the device's heartbeat or maximum reporting interval, the COV settings, how quickly a stale value would mislead the report, and how often gateways drop out.
SELECT g.slot::text AS slot,
l.ts::text AS source_ts,
l.value AS locf_value,
CASE WHEN l.ts IS NULL OR g.slot - l.ts > interval '20 minutes'
THEN 'stale' ELSE 'ok' END AS status
FROM generate_series(timestamptz '2026-10-05 08:00+00',
timestamptz '2026-10-05 09:00+00',
interval '15 minutes') AS g(slot)
LEFT JOIN LATERAL (
SELECT r.ts, r.value
FROM reading r
WHERE r.point_id = 1 AND r.ts <= g.slot
ORDER BY r.ts DESC
LIMIT 1
) l ON true
ORDER BY g.slot;
-- slot | source_ts | locf_value | status
-- ------------------------+------------------------+------------+--------
-- 2026-10-05 08:00:00+00 | 2026-10-05 07:56:00+00 | 20.6 | ok
-- 2026-10-05 08:15:00+00 | 2026-10-05 08:03:05+00 | 21 | ok
-- 2026-10-05 08:30:00+00 | 2026-10-05 08:03:05+00 | 21 | stale
-- 2026-10-05 08:45:00+00 | 2026-10-05 08:41:20+00 | 20.4 | ok
-- 2026-10-05 09:00:00+00 | 2026-10-05 08:41:20+00 | 20.4 | okLinear interpolation, and why it is wrong for step data
Interpolation draws a straight line between the reading before and the reading after each slot. For a regularly polled analogue signal that is a reasonable estimate. For COV data it invents values: at 08:00 it returns 22.48, a temperature the zone never had, because it leans towards the 23.5 spike that started at 08:02:10. Compare the LOCF value of 20.6 above.
SELECT g.slot::text AS slot,
round((CASE WHEN n.ts = p.ts THEN p.value
ELSE p.value + (n.value - p.value) * extract(epoch FROM g.slot - p.ts) / extract(epoch FROM n.ts - p.ts) END)::numeric, 2) AS interpolated
FROM generate_series(timestamptz '2026-10-05 08:00+00', timestamptz '2026-10-05 09:00+00', interval '15 minutes') AS g(slot)
CROSS JOIN LATERAL (SELECT ts, value FROM reading r WHERE r.point_id = 1 AND r.ts <= g.slot ORDER BY ts DESC LIMIT 1) p
CROSS JOIN LATERAL (SELECT ts, value FROM reading r WHERE r.point_id = 1 AND r.ts >= g.slot ORDER BY ts LIMIT 1) n
ORDER BY g.slot;
-- slot | interpolated
-- ------------------------+--------------
-- 2026-10-05 08:00:00+00 | 22.48
-- 2026-10-05 08:15:00+00 | 20.81
-- 2026-10-05 08:30:00+00 | 20.58
-- 2026-10-05 08:45:00+00 | 20.48
-- 2026-10-05 09:00:00+00 | 20.83The same operations in TimescaleDB
TimescaleDB packages these patterns as functions. time_bucket_gapfill() generates the missing buckets; locf() and interpolate() fill them, and both accept a prev expression to supply a value from before the queried range, which is the carry-in problem solved above. The TimescaleDB Toolkit adds time_weight(), whose documented methods are linear (alias trapezoidal) and LOCF, and an interpolated_average() accessor that, per its reference page, uses values from the previous and next buckets to interpolate the values at the bucket bounds. A plain average() per bucket does not do that, so the first segment of each bucket starts at its first reading rather than at the value in force before it. They save writing the CTEs, but they are extension functions: the queries stop being portable to plain PostgreSQL, and the Toolkit is documented as a separate API from core TimescaleDB, so check that it is available in your installation or hosting plan (not checked here).
-- Empty buckets filled with the last observed average
SELECT time_bucket_gapfill('15 minutes', ts) AS bucket,
locf(avg(value)) AS value
FROM reading
WHERE point_id = 1
AND ts >= '2026-10-05 08:00+00' AND ts < '2026-10-05 09:00+00'
GROUP BY bucket
ORDER BY bucket;
-- Time-weighted average per bucket (TimescaleDB Toolkit). average() covers each bucket
-- from its first reading only; interpolated_average() with the previous and next
-- summaries also covers the bucket boundaries (see the Toolkit reference).
SELECT time_bucket('15 minutes', ts) AS bucket,
average(time_weight('LOCF', ts, value)) AS twa
FROM reading
WHERE point_id = 1
GROUP BY bucket
ORDER BY bucket;Design decisions, limitations and when not to do this in SQL
- Store the interpretation with the point. Add a column such as
interpolation('step' or 'linear') andmax_age_sto the point table, so reports do not each guess. - Store raw readings, compute views. Keep the irregular readings as delivered and derive regular series in queries or roll-up tables; do not overwrite raw data with filled values.
- Bound every query. The carry-in lookup needs an index on
(point_id, ts); without it each lookup scans the table. - Report coverage. Return the covered seconds or a sample count with every average, so a bucket with five minutes of data is not mistaken for a full one.
Limitations. The examples handle one point; for many points add point_id to the joins and PARTITION BY point_id to the window function. Step weighting assumes the device would have reported a change; if COV notifications were lost (for example during a network outage), the carried value is wrong and only the staleness limit or a periodic poll protects you. Subtracting two timestamptz values gives elapsed time regardless of daylight-saving changes, but buckets and day boundaries for local-time reports need care; the examples use UTC throughout.
When not to do this in SQL. Our editorial view: signal processing such as resampling high-rate vibration data, filtering or spectral analysis is usually better done in an analytics tool (Python, a lakehouse notebook), while SQL suits report-level averages, compliance KPIs and dashboard grids. For roll-up tables built from these queries, see Continuous Aggregates vs Materialized Views; for the bigger picture, the time-series overview. To try the queries, paste them into the PostgreSQL playground.
Frequently asked questions
What is irregular time series data?
A series whose readings are not evenly spaced in time: devices that report on change, polls that drift, or gaps from communication failures. It needs time-aware aggregation rather than a plain average.
How do I calculate a time-weighted average in SQL?
Turn readings into segments (each value until the next reading, using lead()), include the value in force at the window start, multiply each value by its duration and divide by the total duration. The full query is on this page.
How do I fill gaps in time series data in PostgreSQL?
Generate the full grid of buckets with generate_series, left-join your aggregates, then fill empty buckets with the last observation carried forward via a LATERAL lookup, or leave them NULL. TimescaleDB offers time_bucket_gapfill() with locf() and interpolate() for the same job.
Should I use LOCF or linear interpolation?
LOCF for values that hold until reported otherwise (COV data, setpoints, states). Linear interpolation for continuously varying values sampled regularly. Using interpolation on step data produces values that never occurred.
What is date_bin in PostgreSQL?
A function, added in PostgreSQL 14 according to the PostgreSQL 14 release notes, that assigns a timestamp to the start of a fixed-width bin (for example 15 minutes) aligned to an origin you supply. It does not create empty bins; that needs generate_series.
Sources
- PostgreSQL 18 documentation: Date/Time Functions (date_bin)
- PostgreSQL 18 documentation: Set Returning Functions (generate_series)
- PostgreSQL 18 documentation: Window Functions
- PostgreSQL 18 documentation: Table Expressions (LATERAL)
- TimescaleDB Toolkit reference: time_weight()
- TimescaleDB reference: time_bucket_gapfill()
- TimescaleDB reference: locf()
- TimescaleDB reference: interpolate()
- OPC 30030 OPC UA for BACnet: Terms used from the BACnet standard
- TimescaleDB Toolkit reference: time_weight() function
- TimescaleDB Toolkit reference: interpolated_average()
- PostgreSQL 14 release notes
Checked 27 October 2026.
How we research these articles: our editorial method. Research-based from official documentation; SQL examples are run on a local database where the page says so. CodeWithSQL earns nothing from the vendors mentioned.