time_bucket_gapfill()

Bucket rows by time interval while filling gaps in data

Since 1.2.0

Group data into buckets based on time interval, while filling in gaps of missing data. If you don't provide a gapfilling algorithm, such as locf or interpolate, gaps are left as NULL in the returned data.

Samples

Create a hypertable

CREATE TABLE sensor_data (
   time TIMESTAMPTZ NOT NULL,
   sensor_id INTEGER,
   temperature DOUBLE PRECISION
) WITH (tsdb.hypertable);

Fill missing hourly sensor data with gapfilling and interpolation

SELECT
  time_bucket_gapfill('1 hour', time) AS hour,
  sensor_id,
  AVG(temperature) AS avg_temp,
  interpolate(AVG(temperature)) AS interpolated_temp,
  locf(AVG(temperature)) AS locf_temp
FROM sensor_data
WHERE time >= NOW() - INTERVAL '1 day'
  AND time < NOW()  -- Must have upper bound for gapfill to work
  AND sensor_id IN (1, 2, 3)
GROUP BY hour, sensor_id
ORDER BY hour;

Returns something like:

hour	sensor_id	avg_temp	interpolated_temp	locf_temp
2025-12-10 09:00:00+00	1	23.54183788470983	23.54183788470983	23.54183788470983
2025-12-10 09:00:00+00	3	23.501428105258427	23.501428105258427	23.501428105258427
2025-12-10 09:00:00+00	2	23.561574556138247	23.561574556138247	23.561574556138247
2025-12-10 10:00:00+00	1	22.51280004019483	22.51280004019483	22.51280004019483
2025-12-10 10:00:00+00	2	22.49593389975133	22.49593389975133	22.49593389975133
2025-12-10 10:00:00+00	3	22.512339294279585	22.512339294279585	22.512339294279585
2025-12-10 11:00:00+00	1	21.258753537111346	21.258753537111346	21.258753537111346
2025-12-10 11:00:00+00	2	21.320573198758982	21.320573198758982	21.320573198758982
2025-12-10 11:00:00+00	3	21.29395273116155	21.29395273116155	21.29395273116155
2025-12-10 12:00:00+00	1	20.01722603628097	20.01722603628097	20.01722603628097
2025-12-10 12:00:00+00	2	19.985153555974925	19.985153555974925	19.985153555974925
2025-12-10 12:00:00+00	3	20.002541510158228	20.002541510158228	20.002541510158228

Fill gaps between the earliest and latest rows in the table

In TimescaleDB 2.28 and later, the gapfill boundaries can come from subquery results instead of constants. For example, use the table's own minimum and maximum timestamps as the gapfill range:
```
SELECT
  time_bucket_gapfill('1 hour', time) AS hour,
  sensor_id,
  locf(AVG(temperature)) AS locf_temp
FROM sensor_data
WHERE time >= (SELECT min(time) FROM sensor_data)
  AND time < (SELECT max(time) FROM sensor_data)
GROUP BY hour, sensor_id
ORDER BY sensor_id, hour;
```
The subqueries must be uncorrelated: a subquery that references a column of the outer query cannot be used to infer the gapfill boundaries.

Use a different bucket width for each group

In TimescaleDB 2.28 and later, bucket_width can also come from a subquery. Combine this with a LATERAL join to gapfill each sensor at its own resolution:

SELECT s.sensor_id, bucket, locf_temp
FROM (VALUES (1, '15 minutes'::interval),
             (2, '1 hour'::interval)) s (sensor_id, bucket_width)
INNER JOIN LATERAL (
  SELECT
    time_bucket_gapfill(s.bucket_width, time) AS bucket,
    locf(AVG(temperature)) AS locf_temp
  FROM sensor_data
  WHERE sensor_id = s.sensor_id
    AND time >= (SELECT min(time) FROM sensor_data)
    AND time < (SELECT max(time) FROM sensor_data)
  GROUP BY bucket
) sq ON true
ORDER BY s.sensor_id, bucket;

Arguments

The syntax is:

SELECT time_bucket_gapfill(
    bucket_width = <interval>,
    ts = <timestamp>,
    start = <timestamp>,
    finish = <timestamp>,
    timezone = '<timezone>'
) AS bucket
FROM <table_name>
WHERE <timestamp> >= <start>
  AND <timestamp> < <finish>
GROUP BY bucket;

Name	Type	Default	Required	Description
`bucket_width`	INTERVAL	INTEGER	-	✔
`ts`	TIMESTAMPTZ	TIMESTAMP	DATE	SMALLINT
`timezone`	TEXT	-	-	The timezone to use for bucketing. For example, `Europe/Berlin`. Available in TimescaleDB 2.9 or later. Does not work for integer-based time. If you have an untyped `start` or `finish` argument and a `timezone` argument, you might run into a problem where you are not passing your arguments for the parameter that you expect. To solve this, either name your arguments or explicitly type cast them.
`start`	TIMESTAMPTZ	INTEGER	-	-
`finish`	TIMESTAMPTZ	INTEGER	-	-

Returns

Column	Type	Description
`time_bucket_gapfill`	TIMESTAMPTZ or INTEGER	The start time of the time bucket. The return type matches the input `time` type.