interpolated_state_periods()
Get the time periods corresponding to a given state from a state aggregate, interpolating values at time bucket boundaries
Since 1.15.0
List the periods when the system is in a specific state from a state aggregate. Periods are defined by the start time and end time.
Unlike state_periods, 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 list all time
periods corresponding to the state OK.
To perform the interpolation, the LAG function is used to get the previous state aggregate.
SELECT bucket, (interpolated_state_periods( summary, 'OK', 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 | start_time | end_time------------------------+------------------------+------------------------ 2020-01-01 00:00:00+00 | 2020-01-01 00:00:11+00 | 2020-01-01 00:15:00+00 2020-01-01 00:01:00+00 | 2020-01-01 00:01:03+00 | 2020-01-01 00:16:00+00Arguments
Section titled “Arguments”The syntax is:
interpolated_state_periods( agg StateAgg, state {TEXT | BIGINT}, start TIMESTAMPTZ, interval INTERVAL, prev StateAgg) RETURNS (TIMESTAMPTZ, TIMESTAMPTZ)| Name | Type | Default | Required | Description |
|---|---|---|---|---|
| agg | StateAgg | - | ✔ | A state aggregate created with state_agg. Must not be NULL |
| state | TEXT | BIGINT | - | ✔ | The state to query |
| 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; no period then begins before the first timestamp in agg |
Returns
Section titled “Returns”| Column | Type | Description |
|---|---|---|
| start_time | TIMESTAMPTZ | The time when the state started (inclusive) |
| end_time | TIMESTAMPTZ | The time when the state ended (exclusive) |