--- title: time_bucket_gapfill() | Tiger Data Docs description: Bucket rows by time interval while filling gaps in data --- Since [1.2.0](https://github.com/timescale/timescaledb/releases/tag/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 1. **Create a hypertable** ``` CREATE TABLE sensor_data ( time TIMESTAMPTZ NOT NULL, sensor_id INTEGER, temperature DOUBLE PRECISION ) WITH (tsdb.hypertable); ``` 2. **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 | 3. **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. 4. **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; ``` 5. **Filter gapfilled results** To filter the gapfilled rows, wrap the gapfill query in a common table expression and filter its results. For example, to return only the hours with no readings: ``` WITH gapfilled AS ( SELECT time_bucket_gapfill('1 hour', time) AS hour, sensor_id, count(*) AS reading_count FROM sensor_data WHERE time >= '2025-12-10 09:00:00+00' AND time < '2025-12-10 14:00:00+00' GROUP BY hour, sensor_id ) SELECT * FROM gapfilled WHERE reading_count IS NULL ORDER BY hour; ``` Returns something like: | hour | sensor\_id | reading\_count | | ---------------------- | ---------- | -------------- | | 2025-12-10 10:00:00+00 | 1 | | | 2025-12-10 12:00:00+00 | 1 | | Warning Do not use `HAVING` to filter gapfilled rows. `HAVING` is applied before gapfilling runs, so it filters the aggregation results instead. Do not wrap the gapfill query in a subquery either, because the query planner often optimizes the subquery away. ## Arguments The syntax is: ``` SELECT time_bucket_gapfill( bucket_width = , ts = , start = , finish = , timezone = '' ) AS bucket FROM WHERE >= AND < 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. |