---
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 |

## 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. |