time_bucket_gapfill()

The time_bucket_gapfill function works similar to time_bucket but also activates gap filling for the interval between start and finish. It can only be used with an aggregation query. Values outside of start and finish will pass through but no gap filling will be done outside of the specified range.

Starting with version 1.3.0, start and finish are optional arguments and will be inferred from the WHERE clause if not supplied as arguments.

tip

We recommend using a WHERE clause whenever possible (instead of just start and finish arguments), as start and finish arguments will not filter input rows. Thus without a WHERE clause, this will lead TimescaleDB's planner to select all data and not perform constraint exclusion to exclude chunks from further processing, which would be less performant.

The time_bucket_gapfill must be a top-level expression in a query or subquery, as shown in the above examples. You cannot, for example, do something like round(time_bucket_gapfill(...)) or cast the result of the gapfill call (unless as a subquery where the outer query does the type cast).

Required Arguments

NameTypeDescription
bucket_widthINTERVALA PostgreSQL time interval for how long each bucket is
timeTIMESTAMPThe timestamp to bucket

Optional Arguments

NameTypeDescription
startTIMESTAMPThe start of the gapfill period
finishTIMESTAMPThe end of the gapfill period

Note that explicitly provided start and stop or derived from WHERE clause values need to be simple expressions. Such expressions should be evaluated to constants at the query planning. For example, simple expressions can contain constants or call to now(), but cannot reference to columns of a table.

For Integer Time Inputs

Required Arguments

NameTypeDescription
bucket_widthINTEGERinteger interval for how long each bucket is
timeINTEGERThe timestamp to bucket

Optional Arguments

NameTypeDescription
startINTEGERThe start of the gapfill period
finishINTEGERThe end of the gapfill period

Starting with version 1.3.0 start and finish are optional arguments and will be inferred from the WHERE clause if not supplied as arguments.

Sample Usage

Get the metric value every day over the last 7 days:

SELECT
  time_bucket_gapfill('1 day', time) AS day,
  device_id,
  avg(value) AS value
FROM metrics
WHERE time > now() - INTERVAL '1 week' AND time < now()
GROUP BY day, device_id
ORDER BY day;

           day          | device_id | value
------------------------+-----------+-------
 2019-01-10 01:00:00+01 |         1 |
 2019-01-11 01:00:00+01 |         1 |   5.0
 2019-01-12 01:00:00+01 |         1 |
 2019-01-13 01:00:00+01 |         1 |   7.0
 2019-01-14 01:00:00+01 |         1 |
 2019-01-15 01:00:00+01 |         1 |   8.0
 2019-01-16 01:00:00+01 |         1 |   9.0
(7 row)

Get the metric value every day over the last 7 days carrying forward the previous seen value if none is available in an interval:

SELECT
  time_bucket_gapfill('1 day', time) AS day,
  device_id,
  avg(value) AS value,
  locf(avg(value))
FROM metrics
WHERE time > now() - INTERVAL '1 week' AND time < now()
GROUP BY day, device_id
ORDER BY day;

           day          | device_id | value | locf
------------------------+-----------+-------+------
 2019-01-10 01:00:00+01 |         1 |       |
 2019-01-11 01:00:00+01 |         1 |   5.0 |  5.0
 2019-01-12 01:00:00+01 |         1 |       |  5.0
 2019-01-13 01:00:00+01 |         1 |   7.0 |  7.0
 2019-01-14 01:00:00+01 |         1 |       |  7.0
 2019-01-15 01:00:00+01 |         1 |   8.0 |  8.0
 2019-01-16 01:00:00+01 |         1 |   9.0 |  9.0

Get the metric value every day over the last 7 days interpolating missing values:

SELECT
  time_bucket_gapfill('5 minutes', time) AS day,
  device_id,
  avg(value) AS value,
  interpolate(avg(value))
FROM metrics
WHERE time > now() - INTERVAL '1 week' AND time < now()
GROUP BY day, device_id
ORDER BY day;

           day          | device_id | value | interpolate
------------------------+-----------+-------+-------------
 2019-01-10 01:00:00+01 |         1 |       |
 2019-01-11 01:00:00+01 |         1 |   5.0 |         5.0
 2019-01-12 01:00:00+01 |         1 |       |         6.0
 2019-01-13 01:00:00+01 |         1 |   7.0 |         7.0
 2019-01-14 01:00:00+01 |         1 |       |         7.5
 2019-01-15 01:00:00+01 |         1 |   8.0 |         8.0
 2019-01-16 01:00:00+01 |         1 |   9.0 |         9.0

Found an issue on this page?

Report an issue!

Related Content