Skip to content

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.

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+00

The syntax is:

interpolated_state_periods(
agg StateAgg,
state {TEXT | BIGINT},
start TIMESTAMPTZ,
interval INTERVAL,
prev StateAgg
) RETURNS (TIMESTAMPTZ, TIMESTAMPTZ)
NameTypeDefaultRequiredDescription
aggStateAgg-✔A state aggregate created with state_agg. Must not be NULL
stateTEXT | BIGINT-✔The state to query
startTIMESTAMPTZ-✔The start of the interval to be calculated
intervalINTERVAL-✔The length of the interval to be calculated
prevStateAgg-✔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
ColumnTypeDescription
start_timeTIMESTAMPTZThe time when the state started (inclusive)
end_timeTIMESTAMPTZThe time when the state ended (exclusive)