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 time bucket boundaries are interpolated from adjacent state aggregates.

interpolated_state_periods(
  agg StateAgg,
  state [TEXT | BIGINT],
  start TIMESTAMPTZ,
  interval INTERVAL,
  [, prev StateAgg]
) RETURNS (TIMESTAMPTZ, TIMESTAMPTZ)

Arguments

Name	Type	Default	Required	Description
agg	StateAgg	-	✔	A state aggregate created with state_agg
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. If NULL, the first timestamp in aggregate is used as the start of the interval

Returns

Column	Type	Description
start_time	TIMESTAMPTZ	The time when the state started (inclusive)
end_time	TIMESTAMPTZ	The time when the state ended (exclusive)

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 and LEAD functions are used to get the previous and next state aggregates.

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