Gap-fill with linear interpolation in PostgreSQL

A time series with regular intervals (for example a temperature sensor measuring every 5 minutes) often contains holes in practice. I will show three ways to fill in the missing points with linear interpolation between their neighbours: classic PostgreSQL, PostgreSQL 16+/18 and TimescaleDB.

Sample data

CREATE TABLE readings (
    time        timestamptz PRIMARY KEY,
    temperature numeric
);

INSERT INTO readings (time, temperature) VALUES
    ('2024-01-15 10:00+00', 20.0),
    ('2024-01-15 10:05+00', 21.0),
    -- 10:10 is missing
    -- 10:15 is missing
    ('2024-01-15 10:20+00', 24.0),
    ('2024-01-15 10:25+00', 25.0);

We want to fill 10:10 and 10:15 linearly between 21.0 and 24.0, so the result comes out as 22.0 and 23.0.

---

1. Classic PostgreSQL (any version)

The strategy:

1. Generate the complete series with generate_series.
2. LEFT JOIN the original data - the missing points are NULL.
3. For every NULL, find the nearest previous and next known value using the carry-forward partition trick.
4. Interpolate linearly.

WITH grid AS (
    SELECT generate_series(
        '2024-01-15 10:00+00'::timestamptz,
        '2024-01-15 10:25+00'::timestamptz,
        interval '5 minutes'
    ) AS time
),
joined AS (
    SELECT g.time, r.temperature
    FROM grid g
    LEFT JOIN readings r USING (time)
),
flagged AS (
    SELECT time, temperature,
        SUM((temperature IS NOT NULL)::int)
            OVER (ORDER BY time ROWS UNBOUNDED PRECEDING) AS bgrp,
        SUM((temperature IS NOT NULL)::int)
            OVER (ORDER BY time DESC ROWS UNBOUNDED PRECEDING) AS fgrp
    FROM joined
),
neighbours AS (
    SELECT time, temperature,
        FIRST_VALUE(temperature) OVER (PARTITION BY bgrp ORDER BY time)      AS prev_y,
        FIRST_VALUE(time)        OVER (PARTITION BY bgrp ORDER BY time)      AS prev_t,
        FIRST_VALUE(temperature) OVER (PARTITION BY fgrp ORDER BY time DESC) AS next_y,
        FIRST_VALUE(time)        OVER (PARTITION BY fgrp ORDER BY time DESC) AS next_t
    FROM flagged
)
SELECT
    time,
    CASE
        WHEN temperature IS NOT NULL THEN temperature
        WHEN prev_y IS NOT NULL AND next_y IS NOT NULL
            THEN prev_y + (next_y - prev_y)
                          * EXTRACT(epoch FROM (time - prev_t))
                          / EXTRACT(epoch FROM (next_t - prev_t))
    END AS temperature
FROM neighbours
ORDER BY time;

How bgrp/fgrp work: a cumulative sum of IS NOT NULL from the front (or from the back) creates a partition in which all NULL rows after the last known point share the same number. FIRST_VALUE in such a partition returns the previous (or next) known value.

---

2. PostgreSQL 16+ (and 18) - IGNORE NULLS

Since PostgreSQL 16 (and so in PG 17/18 too) the standard SQL clause IGNORE NULLS is available for LAG, LEAD, FIRST_VALUE, LAST_VALUE and NTH_VALUE. That removes the whole bgrp/fgrp construction:

WITH grid AS (
    SELECT generate_series(
        '2024-01-15 10:00+00'::timestamptz,
        '2024-01-15 10:25+00'::timestamptz,
        interval '5 minutes'
    ) AS time
),
joined AS (
    SELECT g.time, r.temperature
    FROM grid g
    LEFT JOIN readings r USING (time)
),
neighbours AS (
    SELECT
        time,
        temperature,
        LAG(temperature)  IGNORE NULLS OVER (ORDER BY time) AS prev_y,
        LAG(CASE WHEN temperature IS NOT NULL THEN time END)
                          IGNORE NULLS OVER (ORDER BY time) AS prev_t,
        LEAD(temperature) IGNORE NULLS OVER (ORDER BY time) AS next_y,
        LEAD(CASE WHEN temperature IS NOT NULL THEN time END)
                          IGNORE NULLS OVER (ORDER BY time) AS next_t
    FROM joined
)
SELECT
    time,
    CASE
        WHEN temperature IS NOT NULL THEN temperature
        WHEN prev_y IS NOT NULL AND next_y IS NOT NULL
            THEN prev_y + (next_y - prev_y)
                          * EXTRACT(epoch FROM (time - prev_t))
                          / EXTRACT(epoch FROM (next_t - prev_t))
    END AS temperature
FROM neighbours
ORDER BY time;

Watch this detail: LAG(time) IGNORE NULLS skips rows where time is NULL. But time in our table is never NULL. That is why we wrap time in CASE WHEN temperature IS NOT NULL THEN time END, so that IGNORE NULLS follows temperature.

Even with this small wrinkle the query is considerably more readable than the version with bgrp/fgrp.

---

3. TimescaleDB - time_bucket_gapfill + interpolate

With the TimescaleDB extension a single query is enough:

SELECT
    time_bucket_gapfill('5 minutes', time) AS bucket,
    interpolate(avg(temperature))          AS temperature
FROM readings
WHERE time >= '2024-01-15 10:00+00'
  AND time <= '2024-01-15 10:25+00'
GROUP BY bucket
ORDER BY bucket;

time_bucket_gapfill generates the complete series of 5-minute buckets, and interpolate() then fills in the missing points with linear interpolation between neighbours.

Advantages:

• no WITH chain, no window functions
• works naturally with GROUP BY (for example per sensor_id)
• there is also locf() for the last observation carried forward variant (for step-like state values)

Limitations:

• requires the TimescaleDB extension
• WHERE time BETWEEN ... AND ... is mandatory - gapfill needs to know the bounds

---

When to use what

Situation	Recommendation
PostgreSQL ≤ 15	Carry-forward partition trick (section 1)
PostgreSQL 16+ / 18	IGNORE NULLS (section 2) - more readable
TimescaleDB available	time_bucket_gapfill + interpolate (section 3)

Linear interpolation suits quantities that change continuously between measurements (temperature, pressure, humidity, water level). For step-like state signals (an online/offline flag, a categorical state) choose LOCF instead. Linear interpolation makes no sense there.

Do not forget: an interpolated value is not a measured value. For larger gaps (a manually set limit, for example > 60 minutes) it is better to leave the gap empty than to compute a value from the endpoints of a large range, where the data may behave non-linearly.