interpolated_state_timeline()

interpolated_state_timeline()

Returns the interpolated timeline of states in a timeline aggregate.

Unlike state_timeline, you can use this function across multiple state aggregates that cover different time buckets. Any missing values at the time bucket boundaries are interpolated from adjacent TimelineAggs.

2. interpolated_state_timeline(

4. tws [StateAgg | TimelineAgg],

6. start TIMESTAMPTZ,

8. interval INTERVAL,

10. prev [StateAgg | TimelineAgg],

12. next [StateAgg | TimelineAgg]

14. ) RETURNS (TIMESTAMPTZ, TIMESTAMPTZ)

warning

Experimental features could have bugs. They might not be backwards compatible, and could be removed in future releases. Use these features at your own risk, and do not use any experimental features in production.

Required arguments

Name	Type	Description
`aggregate`	`TimelineAgg`	Previously created state_agg aggregate
`start`	`TIMESTAMPTZ`	The start of the interval which this function should cover (if there is a preceeding point)
`interval`	`INTERVAL`	The length of the interval

Optional arguments

Name	Type	Description
`prev`	`TimelineAgg`	The `TimelineAgg` from the prior interval, used to interpolate the value at `start`. If `NULL`, the first timestamp in `aggregate` is used as the start of the interval.
`next`	`TimelineAgg`	The `TimelineAgg` from the following interval, used to interpolate the value at `start` + `interval`. If `NULL`, the last timestamp in `aggregate` is used as the end of the interval.

Returns

Column	Type	Description
`state`	`TEXT` or `BIGINT`	A state found in the `TimelineAgg`
`start_time`	`TIMESTAMPTZ`	The time the state started at (inclusive)
`end_time`	`TIMESTAMPTZ`	The time the state ended at (exclusive)

Sample usage

Getting the interpolated history of states in a timeline aggregate:

2. SELECT

4. bucket,

6. (toolkit_experimental.interpolated_state_timeline(

8. summary,

10. bucket,

12. '15 min',

14. LAG(summary) OVER (ORDER by bucket),

16. LEAD(summary) OVER (ORDER by bucket)

18. )).*

20. FROM (

22. SELECT

24. time_bucket('1 min'::interval, ts) AS bucket,

26. toolkit_experimental.timeline_agg(ts, state) AS summary

28. FROM states_test

30. GROUP BY time_bucket('1 min'::interval, ts)

32. ) t;

Example output:

2. bucket         | state |       start_time       |        end_time

4. ------------------------+-------+------------------------+------------------------

6. 2020-01-01 00:00:00+00 | START | 2020-01-01 00:00:00+00 | 2020-01-01 00:00:11+00

8. 2020-01-01 00:00:00+00 | OK    | 2020-01-01 00:00:11+00 | 2020-01-01 00:15:00+00

10. 2020-01-01 00:01:00+00 | ERROR | 2020-01-01 00:01:00+00 | 2020-01-01 00:01:03+00

12. 2020-01-01 00:01:00+00 | OK    | 2020-01-01 00:01:03+00 | 2020-01-01 00:16:00+00

14. 2020-01-01 00:02:00+00 | STOP  | 2020-01-01 00:02:00+00 | 2020-01-01 00:02:00+00