我的书签
添加书签
移除书签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 pass through but no gap filling is done outside of the specified range.
important
The time_bucket_gapfill function must be a top-level expression in a query or subquery, as shown in these examples. You cannot, for example, do something like round(time_bucket_gapfill(...)) or cast the result of the gapfill call. The only exception is if you use it as a subquery, where the outer query does the type cast.
For more information about gapfilling and interpolation functions, see the hyperfunctions documentation.
Required arguments
| Name | Type | Description |
|---|---|---|
bucket_width |
INTERVAL | A PostgreSQL time interval for how long each bucket is |
time |
TIMESTAMP | The timestamp to bucket |
Optional arguments
| Name | Type | Description |
|---|---|---|
start |
TIMESTAMP | The start of the gapfill period |
finish |
TIMESTAMP | The end of the gapfill period |
In TimescaleDB 1.3 and later, start and finish are optional arguments. If they are not supplied, the parameters are inferred from the WHERE clause. We recommend using a WHERE clause if possible, instead of start and finish arguments. This is because start and finish arguments do not filter input rows. If you do not provide a WHERE clause, TimescaleDB’s planner selects all data, and does not perform constraint exclusion to exclude chunks from further processing, which is less performant.
Values explicitly provided in start and stop arguments, or values derived from WHERE clause values, must be simple expressions. They should be evaluated to constants at query planning. For example, simple expressions can contain constants or call to now(), but cannot reference columns of a table.
For integer time inputs
Required arguments
| Name | Type | Description |
|---|---|---|
bucket_width |
INTEGER | integer interval for how long each bucket is |
time |
INTEGER | The timestamp to bucket |
Optional arguments
| Name | Type | Description |
|---|---|---|
start |
INTEGER | The start of the gapfill period |
finish |
INTEGER | The end of the gapfill period |
In TimescaleDB 1.3 and later, start and finish are optional arguments. If they are not supplied, the parameters are inferred from the WHERE clause. We recommend using a WHERE clause if possible, instead of start and finish arguments. This is because start and finish arguments do not filter input rows. If you do not provide a WHERE clause, TimescaleDB’s planner selects all data, and does not perform constraint exclusion to exclude chunks from further processing, which is less performant.
Sample usage
Get the metric value every day over the last seven days:
2. SELECT
4. time_bucket_gapfill('1 day', time) AS day,
6. device_id,
8. avg(value) AS value
10. FROM metrics
12. WHERE time > now() - INTERVAL '1 week' AND time < now()
14. GROUP BY day, device_id
16. ORDER BY day;
18. day | device_id | value
20. ------------------------+-----------+-------
22. 2019-01-10 01:00:00+01 | 1 |
24. 2019-01-11 01:00:00+01 | 1 | 5.0
26. 2019-01-12 01:00:00+01 | 1 |
28. 2019-01-13 01:00:00+01 | 1 | 7.0
30. 2019-01-14 01:00:00+01 | 1 |
32. 2019-01-15 01:00:00+01 | 1 | 8.0
34. 2019-01-16 01:00:00+01 | 1 | 9.0
36. (7 row)
Get the metric value every day over the last seven days, carrying forward the previous seen value if none is available in an interval:
2. SELECT
4. time_bucket_gapfill('1 day', time) AS day,
6. device_id,
8. avg(value) AS value,
10. locf(avg(value))
12. FROM metrics
14. WHERE time > now() - INTERVAL '1 week' AND time < now()
16. GROUP BY day, device_id
18. ORDER BY day;
20. day | device_id | value | locf
22. ------------------------+-----------+-------+------
24. 2019-01-10 01:00:00+01 | 1 | |
26. 2019-01-11 01:00:00+01 | 1 | 5.0 | 5.0
28. 2019-01-12 01:00:00+01 | 1 | | 5.0
30. 2019-01-13 01:00:00+01 | 1 | 7.0 | 7.0
32. 2019-01-14 01:00:00+01 | 1 | | 7.0
34. 2019-01-15 01:00:00+01 | 1 | 8.0 | 8.0
36. 2019-01-16 01:00:00+01 | 1 | 9.0 | 9.0
Get the metric value every day over the last seven days, interpolating missing values:
2. SELECT
4. time_bucket_gapfill('1 day', time) AS day,
6. device_id,
8. avg(value) AS value,
10. interpolate(avg(value))
12. FROM metrics
14. WHERE time > now() - INTERVAL '1 week' AND time < now()
16. GROUP BY day, device_id
18. ORDER BY day;
20. day | device_id | value | interpolate
22. ------------------------+-----------+-------+-------------
24. 2019-01-10 01:00:00+01 | 1 | |
26. 2019-01-11 01:00:00+01 | 1 | 5.0 | 5.0
28. 2019-01-12 01:00:00+01 | 1 | | 6.0
30. 2019-01-13 01:00:00+01 | 1 | 7.0 | 7.0
32. 2019-01-14 01:00:00+01 | 1 | | 7.5
34. 2019-01-15 01:00:00+01 | 1 | 8.0 | 8.0
36. 2019-01-16 01:00:00+01 | 1 | 9.0 | 9.0
