interpolated_state_timeline()
Get a timeline of all states from a state aggregate, interpolating values at time bucket boundaries
Since 1.15.0
Get a timeline of all states, showing each time a state is entered and exited.
Unlike state_timeline, you can use this function across multiple state aggregates that cover
different time buckets. Any missing values at the start of a time bucket are interpolated from the previous state
aggregate.
Samples
Section titled “Samples”Given state aggregates bucketed by 1-minute intervals, interpolate the states at the bucket boundaries and get the history of all states.
To perform the interpolation, the LAG function is used to get the previous state aggregate.
SELECT bucket, (interpolated_state_timeline( summary, bucket, '15 min', LAG(summary) OVER (ORDER by bucket) )).*FROM ( SELECT time_bucket('1 min'::interval, ts) AS bucket, state_agg(ts, state) AS summary FROM states_test GROUP BY time_bucket('1 min'::interval, ts)) t;Returns:
bucket | state | start_time | end_time------------------------+-------+------------------------+------------------------ 2020-01-01 00:00:00+00 | START | 2020-01-01 00:00:00+00 | 2020-01-01 00:00:11+00 2020-01-01 00:00:00+00 | OK | 2020-01-01 00:00:11+00 | 2020-01-01 00:15:00+00 2020-01-01 00:01:00+00 | ERROR | 2020-01-01 00:01:00+00 | 2020-01-01 00:01:03+00 2020-01-01 00:01:00+00 | OK | 2020-01-01 00:01:03+00 | 2020-01-01 00:16:00+00 2020-01-01 00:02:00+00 | STOP | 2020-01-01 00:02:00+00 | 2020-01-01 00:17:00+00Arguments
Section titled “Arguments”The syntax is:
interpolated_state_timeline( agg StateAgg, start TIMESTAMPTZ, interval INTERVAL, prev StateAgg) RETURNS (TEXT, TIMESTAMPTZ, TIMESTAMPTZ)
interpolated_state_int_timeline( agg StateAgg, start TIMESTAMPTZ, interval INTERVAL, prev StateAgg) RETURNS (BIGINT, TIMESTAMPTZ, TIMESTAMPTZ)| Name | Type | Default | Required | Description |
|---|---|---|---|---|
| agg | StateAgg | - | ✔ | A state aggregate created with state_agg. Must not be NULL |
| start | TIMESTAMPTZ | - | ✔ | The start of the interval to be calculated |
| interval | INTERVAL | - | ✔ | The length of the interval to be calculated |
| prev | StateAgg | - | ✔ | The state aggregate from the prior interval, used to interpolate the value at start. Pass NULL if there is none; the first period then begins at the first timestamp in agg |
Returns
Section titled “Returns”| Column | Type | Description |
|---|---|---|
| state | TEXT | BIGINT | A state found in the state aggregate |
| start_time | TIMESTAMPTZ | The time when the state started (inclusive) |
| end_time | TIMESTAMPTZ | The time when the state ended (exclusive) |