time_bucket_gapfill() functions

CommunityCommunity functions are available under Timescale Community Edition. Click to learn more.

Aggregate data by time interval, while filling in gaps of missing data.

time_bucket_gapfill works similarly to time_bucket, but adds gapfilling capabilities. The other functions in this group must be used in the same query as time_bucket_gapfill. They control how missing values are treated.

Important

time_bucket_gapfill must be used as a top-level expression in a query or subquery. You cannot, for example, nest time_bucket_gapfill in another function (such as round(time_bucket_gapfill(...))), or cast the result of the gapfilling call. If you need to cast, you can use time_bucket_gapfill in a subquery, and let the outer query do the type cast.

Functions in this group

time_bucket_gapfill: Bucket rows by time interval while filling gaps in data

interpolate: Fill in missing values by linear interpolation
locf: Fill in missing values by carrying the last observed value forward

Function details

time_bucket_gapfill(
  bucket_width INTERVAL | INTEGER,
  time TIMESTAMPTZ | INTEGER,
  [, timezone TEXT]
  [, start TIMESTAMPTZ | INTEGER]
  [, finish TIMESTAMPTZ | INTEGER]
) RETURNS TIMESTAMPTZ

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.

Required arguments

Name	Type	Description
`bucket_width`	`INTERVAL`, `INTEGER`	A PostgreSQL time interval to specify the length of each bucket. For example, use `1 day` to get daily buckets. Use `INTEGER` only if your time column is integer-based.
`time`	`TIMESTAMPTZ`, `INTEGER`	The timestamp on which to base the bucket

Optional arguments

Name	Type	Description
`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`	The start of the period to gapfill. Values before `start` are passed through, but no gapfilling is performed. Use `INTEGER` only if your time column is integer-based. Best practice is to use the `WHERE` clause. Specifying `start` is legacy. The `WHERE` is more performant, because the query planner can filter out chunks by constraint exclusion.
`finish`	`TIMESTAMPTZ`, `INTEGER`	The end of the period to gapfill. Values after `finish` are passed through, but no gapfilling is performed. Use `INTEGER` only if your time column is integer-based. Best practice is to use the `WHERE` clause. Specifying `finish` is legacy. The `WHERE` is more performant, because the query planner can filter out chunks by constraint exclusion.

Returns

Column	Type	Description
`time_bucket_gapfill`	`TIMESTAMPTZ`	The start time of the time bucket.

interpolate(
  value SMALLINT | INTEGER | BIGINT | REAL | DOUBLE PRECISION
  [, prev EXPRESSION]
  [, next EXPRESSION]
) RETURNS SMALLINT | INTEGER | BIGINT | REAL | DOUBLE PRECISION

Fill in missing values by linear interpolation. Use in the same query as time_bucket_gapfill. interpolate cannot be nested inside another function call.

Required arguments

Name	Type	Description
`value`	`SMALLINT`, `INTEGER`, `BIGINT`, `REAL`, `DOUBLE PRECISION`	The value to interpolate

Optional arguments

Name	Type	Description
`prev`	`EXPRESSION`	If no previous value is available for gapfilling, use the `prev` lookup expression to get a previous value. For example, you can use `prev` to fill in the first bucket in a queried time range. The expression must return a `(time, value)` tuple with types corresponding to the bucketed times and values.
`next`	`EXPRESSION`	If no next value is available for gapfilling, use the `next` lookup expression to get a next value. For example, you can use `next` to fill in the last bucket in a queried time range. The expression must return a `(time, value)` tuple with types corresponding to the bucketed times and values.

Returns

Column	Type	Description
`interpolate`	`SMALLINT`, `INTEGER`, `BIGINT`, `REAL`, `DOUBLE PRECISION`	The gapfilled value. The return type is the type of `value`.

locf(
  value ANY ELEMENT
  [, prev EXPRESSION]
  [, treat_null_as_missing BOOLEAN]
) RETURNS ANY ELEMENT

Fill in missing values by carrying the last observed value forward. Use in the same query as time_bucket_gapfill. locf cannot be nested inside another function call.

Required arguments

Name	Type	Description
`value`	`ANY ELEMENT`	The value to carry forward

Optional arguments

Name	Type	Description
`prev`	`EXPRESSION`	If no previous value is available for gapfilling, use the `prev` lookup expression to get a previous value. For example, you can use `prev` to fill in the first bucket in a queried time range.
`treat_null_as_missing`	`BOOLEAN`	When `true`, `NULL` values are ignored, and only non-`NULL` values are carried forward.

Returns

Column	Type	Description
`locf`	`ANY ELEMENT`	The gapfilled value. The return type is the type of `value`.

Extended examples

Use `time_bucket_gapfill` without a gapfilling algorithm

Get the daily average metric value. Use time_bucket_gapfill without specifying a gapfilling algorithm. This leaves the missing values as NULL:


SELECT time_bucket_gapfill('1 day', time) AS day,
    avg(value) as value
    FROM metrics
    WHERE time > '2021-12-31 00:00:00+00'::timestamptz
        AND time < '2022-01-10 00:00:00-00'::timestamptz
    GROUP BY day
    ORDER BY day desc;


day                    |              value
-----------------------+--------------------
2022-01-09 00:00:00+00 |                   
2022-01-08 00:00:00+00 |  48.61293155993108
2022-01-07 00:00:00+00 | 54.388267525986485
2022-01-06 00:00:00+00 |                   
2022-01-05 00:00:00+00 | 58.257520634785266
2022-01-04 00:00:00+00 |  46.09172424261765
2022-01-03 00:00:00+00 |  42.53498707820027
2022-01-02 00:00:00+00 |                   
2022-01-01 00:00:00+00 |  47.84420001415975
2021-12-31 00:00:00+00 |                   
(10 rows)

Use `time_bucket_gapfill` and carry last value forward

Get the daily average metric value. Use locf to carry the last value forward if a value is missing. Note that avg is nested inside locf, and not the other way around.


SELECT time_bucket_gapfill('1 day', time) AS day,
    locf(avg(value)) as value
    FROM metrics
    WHERE time > '2021-12-31 00:00:00+00'::timestamptz
        AND time < '2022-01-10 00:00:00-00'::timestamptz
    GROUP BY day
    ORDER BY day desc;


day                    |              value
-----------------------+--------------------
2022-01-09 00:00:00+00 |  48.61293155993108
2022-01-08 00:00:00+00 |  48.61293155993108
2022-01-07 00:00:00+00 | 54.388267525986485
2022-01-06 00:00:00+00 | 58.257520634785266
2022-01-05 00:00:00+00 | 58.257520634785266
2022-01-04 00:00:00+00 |  46.09172424261765
2022-01-03 00:00:00+00 |  42.53498707820027
2022-01-02 00:00:00+00 |  47.84420001415975
2022-01-01 00:00:00+00 |  47.84420001415975
2021-12-31 00:00:00+00 |                   
(10 rows)

Use `time_bucket_gapfill` and use linear interpolation

Get the daily average metric value. Use interpolate to linearly interpolate the value if it is missing. Note that avg is nested inside interpolate.


SELECT time_bucket_gapfill('1 day', time) AS day,
    interpolate(avg(value)) as value
    FROM metrics
    WHERE time > '2021-12-31 00:00:00+00'::timestamptz
        AND time < '2022-01-10 00:00:00-00'::timestamptz
    GROUP BY day
    ORDER BY day desc;


day                    |              value
-----------------------+--------------------
2022-01-09 00:00:00+00 |                   
2022-01-08 00:00:00+00 |  48.61293155993108
2022-01-07 00:00:00+00 | 54.388267525986485
2022-01-06 00:00:00+00 |  56.32289408038588
2022-01-05 00:00:00+00 | 58.257520634785266
2022-01-04 00:00:00+00 |  46.09172424261765
2022-01-03 00:00:00+00 |  42.53498707820027
2022-01-02 00:00:00+00 | 45.189593546180014
2022-01-01 00:00:00+00 |  47.84420001415975
2021-12-31 00:00:00+00 |                   
(10 rows)

Use `time_bucket_gapfill` with a timezone argument

Get the daily average metric value, using Europe/Berlin as the timezone. Note that daily time buckets now start at 23:00 UTC, which is equivalent to midnight in Berlin for the selected dates:


SELECT time_bucket_gapfill('1 day', time, 'Europe/Berlin') AS day,
    interpolate(avg(value)) as value
    FROM metrics
    WHERE time > '2021-12-31 00:00:00+00'::timestamptz
        AND time < '2022-01-10 00:00:00-00'::timestamptz
    GROUP BY day
    ORDER BY day desc;


day                    |              value
-----------------------+--------------------
2022-01-09 23:00:00+00 |                   
2022-01-08 23:00:00+00 |  48.65079127913703
2022-01-07 23:00:00+00 |  47.31847777099154
2022-01-06 23:00:00+00 |  55.98845740343859
2022-01-05 23:00:00+00 |  55.61667401777108
2022-01-04 23:00:00+00 |  58.74115574522012
2022-01-03 23:00:00+00 |  45.77993635988273
2022-01-02 23:00:00+00 |  41.78689923453202
2022-01-01 23:00:00+00 | 24.324313477743974
2021-12-31 23:00:00+00 |  48.86680377661261
2021-12-30 23:00:00+00 |                   
(11 rows)

time_bucket_gapfill() functions

Important

Functions in this group

Bucket

Interpolator

Function details