state_periods()

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.

If you have multiple state aggregates and need to interpolate the state across interval boundaries, use interpolated_state_periods.

Samples

Create a state aggregate and list all periods corresponding to the state OK.

SELECT start_time, end_time FROM state_periods(
  (SELECT state_agg(ts, state) FROM states_test),
  'OK',
);

Returns:

       start_time       |        end_time
------------------------+------------------------
 2020-01-01 00:00:11+00 | 2020-01-01 00:01:00+00
 2020-01-01 00:01:03+00 | 2020-01-01 00:02:00+00

Arguments

The syntax is:

state_periods(
    agg StateAgg,
    state [TEXT | BIGINT]
) RETURNS (TIMESTAMPTZ, TIMESTAMPTZ)

Name	Type	Default	Required	Description
agg	StateAgg	-	✔	A state aggregate created using state_agg
state	TEXT	BIGINT	-	✔

Returns

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