time_bucket_gapfill()

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

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.