into_values()

Expand the state aggregate into a set of rows, displaying the duration of each state

Since 1.15.0

Unpack the state aggregate into a set of rows with two columns, displaying the duration of each state. By default, the columns are named state and duration. You can rename them using the same method as renaming a table.

Samples

Create a state aggregate from the table states_test. The time column is named time, and the state column contains text values corresponding to different states of a system. Use into_values to display the data from the state aggregate.

SELECT state, duration FROM into_values(
  (SELECT state_agg(time, state) FROM states_test)
);

Returns:

state | duration
------+----------
ERROR | 00:00:03
OK    | 00:01:46
START | 00:00:11

The syntax is:

into_values(
  agg StateAgg
) RETURNS (TEXT, INTERVAL)

into_int_values(
  agg StateAgg
) RETURNS (INT, INTERVAL)

Name	Type	Default	Required	Description
agg	StateAgg	-	✔	A state aggregate created with [state_agg][state_agg]

Returns

Column	Type	Description
state	TEXT	BIGINT	A state found in the state aggregate
duration	INTERVAL	The total time spent in that state

[state_agg]: /api-reference/timescaledb/hyperfunctions/state-tracking/state_agg/state_agg## Arguments