time_bucket_gapfill()

Community

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:

  1. SELECT
  2. time_bucket_gapfill('1 day', time) AS day,
  3. device_id,
  4. avg(value) AS value
  5. FROM metrics
  6. WHERE time > now() - INTERVAL '1 week' AND time < now()
  7. GROUP BY day, device_id
  8. ORDER BY day;
  9. day | device_id | value
  10. ------------------------+-----------+-------
  11. 2019-01-10 01:00:00+01 | 1 |
  12. 2019-01-11 01:00:00+01 | 1 | 5.0
  13. 2019-01-12 01:00:00+01 | 1 |
  14. 2019-01-13 01:00:00+01 | 1 | 7.0
  15. 2019-01-14 01:00:00+01 | 1 |
  16. 2019-01-15 01:00:00+01 | 1 | 8.0
  17. 2019-01-16 01:00:00+01 | 1 | 9.0
  18. (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:

  1. SELECT
  2. time_bucket_gapfill('1 day', time) AS day,
  3. device_id,
  4. avg(value) AS value,
  5. locf(avg(value))
  6. FROM metrics
  7. WHERE time > now() - INTERVAL '1 week' AND time < now()
  8. GROUP BY day, device_id
  9. ORDER BY day;
  10. day | device_id | value | locf
  11. ------------------------+-----------+-------+------
  12. 2019-01-10 01:00:00+01 | 1 | |
  13. 2019-01-11 01:00:00+01 | 1 | 5.0 | 5.0
  14. 2019-01-12 01:00:00+01 | 1 | | 5.0
  15. 2019-01-13 01:00:00+01 | 1 | 7.0 | 7.0
  16. 2019-01-14 01:00:00+01 | 1 | | 7.0
  17. 2019-01-15 01:00:00+01 | 1 | 8.0 | 8.0
  18. 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:

  1. SELECT
  2. time_bucket_gapfill('5 minutes', time) AS day,
  3. device_id,
  4. avg(value) AS value,
  5. interpolate(avg(value))
  6. FROM metrics
  7. WHERE time > now() - INTERVAL '1 week' AND time < now()
  8. GROUP BY day, device_id
  9. ORDER BY day;
  10. day | device_id | value | interpolate
  11. ------------------------+-----------+-------+-------------
  12. 2019-01-10 01:00:00+01 | 1 | |
  13. 2019-01-11 01:00:00+01 | 1 | 5.0 | 5.0
  14. 2019-01-12 01:00:00+01 | 1 | | 6.0
  15. 2019-01-13 01:00:00+01 | 1 | 7.0 | 7.0
  16. 2019-01-14 01:00:00+01 | 1 | | 7.5
  17. 2019-01-15 01:00:00+01 | 1 | 8.0 | 8.0
  18. 2019-01-16 01:00:00+01 | 1 | 9.0 | 9.0