state_timeline()

Get a timeline of all states from a state aggregate

Since 1.15.0

Get a timeline of all states, showing each time a state is entered and exited.

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

Samples

Get the history of states from a state aggregate.

SELECT state, start_time, end_time
  FROM state_timeline(
    (SELECT state_agg(ts, state) FROM states_test)
  );

Returns:

 state |       start_time       |        end_time
-------+------------------------+------------------------
 START | 2020-01-01 00:00:00+00 | 2020-01-01 00:00:11+00
 OK    | 2020-01-01 00:00:11+00 | 2020-01-01 00:01:00+00
 ERROR | 2020-01-01 00:01:00+00 | 2020-01-01 00:01:03+00
 OK    | 2020-01-01 00:01:03+00 | 2020-01-01 00:02:00+00
 STOP  | 2020-01-01 00:02:00+00 | 2020-01-01 00:02:00+00

Arguments

The syntax is:

state_timeline(
    agg StateAgg
) RETURNS (TEXT, TIMESTAMPTZ, TIMESTAMPTZ)

state_int_timeline(
    agg StateAgg
) RETURNS (BIGINT, TIMESTAMPTZ, TIMESTAMPTZ)

Name	Type	Default	Required	Description
agg	StateAgg	-	✔	The aggregate from which to get a timeline

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)