Introduction

Given a system or value that switches between discrete states, aggregate the amount of time spent in each state. For example, you can use the compact_state_agg functions to track how much time a system spends in error, running, or starting states.

compact_state_agg is designed to work with a relatively small number of states. It might not perform well on datasets where states are mostly distinct between rows.

If you need to track when each state is entered and exited, use the state_agg functions. If you need to track the liveness of a system based on a heartbeat signal, consider using the heartbeat_agg functions.

Related hyperfunction groups
Warning

This function group includes some experimental functions. Experimental functions might change or be removed in future releases. We do not recommend using them in production. Experimental functions are marked with an Experimental tag.

Aggregate

compact_state_agg
ExperimentalAggregate state data into a state aggregate for further analysis

Accessor

duration_in
ExperimentalCalculate the total time spent in a given state from a state aggregate
interpolated_duration_in
ExperimentalCalculate the total time spent in a given state from a state aggregate, interpolating values at time bucket boundaries
into_values
ExperimentalExpand a state aggregate into a set of rows displaying the duration of each state

Rollup

rollup
ExperimentalCombine multiple state aggregates
compact_state_agg(
ts TIMESTAMPTZ,
value {TEXT | BIGINT}
) RETURNS StateAgg

Aggregate a dataset containing state data into a state aggregate to track the time spent in each state.

Required arguments
NameTypeDescription
tsTIMESTAMPTZTimestamps associated with each state reading
valueTEXT, BIGINTThe state at that time
Returns
ColumnTypeDescription
aggStateAggAn object storing the total time spent in each state
Examples

Create a state aggregate to track the status of some devices:

SELECT toolkit_experimental.compact_state_agg(time, status) FROM devices;
duration_in(
agg StateAgg,
state {TEXT | BIGINT}
[, start TIMESTAMPTZ]
[, interval INTERVAL]
) RETURNS INTERVAL

Given a state aggregate, calculate the total time spent in the given state. If you need to interpolate missing values across time bucket boundaries, use interpolated_duration_in.

Required arguments
NameTypeDescription
aggStateAggA state aggregate created with compact_state_agg
stateTEXT, BIGINTThe state to query
Optional arguments
NameTypeDescription
startTIMESTAMPTZIf specified, only the time in the state after this time is returned.
intervalINTERVALIf specified, only the time in the state from the start time to the end of the interval is returned.
Returns
ColumnTypeDescription
duration_inINTERVALThe time spent in the given state. Displayed in days, hh:mm:ss, or a combination of the two.
Examples

Create a test table that tracks when a system switches between starting, running, and error states. Query the table for the time spent in the running state.

If you prefer to see the result in seconds, EXTRACT the epoch from the returned result:

SET timezone TO 'UTC';
CREATE TABLE states(time TIMESTAMPTZ, state TEXT);
INSERT INTO states VALUES
('1-1-2020 10:00', 'starting'),
('1-1-2020 10:30', 'running'),
('1-3-2020 16:00', 'error'),
('1-3-2020 18:30', 'starting'),
('1-3-2020 19:30', 'running'),
('1-5-2020 12:00', 'stopping');
SELECT toolkit_experimental.duration_in(
toolkit_experimental.compact_state_agg(time, state),
'running'
) FROM states;
duration_in
---------------
3 days 22:00:00
interpolated_duration_in(
agg StateAgg,
state {TEXT | BIGINT},
start TIMESTAMPTZ,
interval INTERVAL
[, prev StateAgg]
) RETURNS DOUBLE PRECISION

Calculate the total duration in the given state. Unlike duration_in, you can use this function across multiple state aggregates that cover multiple time buckets. Any missing values at the time bucket boundaries are interpolated from adjacent state aggregates.

Required arguments
NameTypeDescription
aggStateAggA state aggregate created with compact_state_agg
stateTEXT, BIGINTThe state to query
startTIMESTAMPTZThe start of the interval to be calculated
intervalINTERVALThe length of the interval to be calculated
Optional arguments
NameTypeDescription
prevStateAggThe 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
ColumnTypeDescription
interpolated_duration_inINTERVALThe total time spent in the queried state. Displayed as days, hh:mm:ss, or a combination of the two.
Examples

Create a test table that tracks when a system switches between starting, running, and error states. Query the table for the time spent in the running state. Use LAG and LEAD to get the neighboring aggregates for interpolation. If you prefer to see the result in seconds, EXTRACT the epoch from the returned result:

SELECT
time,
toolkit_experimental.interpolated_duration_in(
agg,
'running',
time,
'1 day',
LAG(agg) OVER (ORDER BY time)
) FROM (
SELECT
time_bucket('1 day', time) as time,
toolkit_experimental.compact_state_agg(time, state) as agg
FROM
states
GROUP BY time_bucket('1 day', time)
) s;
time | interpolated_duration_in
------------------------+--------------------------
2020-01-01 00:00:00+00 | 13:30:00
2020-01-02 00:00:00+00 | 16:00:00
2020-01-03 00:00:00+00 | 04:30:00
2020-01-04 00:00:00+00 | 12:00:00
into_values(
agg StateAgg
) RETURNS (TEXT, INTERVAL)
into_int_values(
agg StateAgg
) RETURNS (INT, INTERVAL)

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.

Required arguments
NameTypeDescription
aggStateAggA state aggregate created with compact_state_agg
Returns
ColumnTypeDescription
stateTEXT, BIGINTA state found in the state aggregate
durationINTERVALThe total time spent in that state
Examples

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 toolkit_experimental.into_values(
(SELECT toolkit_experimental.compact_state_agg(time, state) FROM states_test)
);
state | duration
------+----------
ERROR | 00:00:03
OK | 00:01:46
START | 00:00:11
rollup(
agg StateAgg
) RETURNS StateAgg

Combine multiple state aggregates into a single state aggregate. For example, you can use rollup to combine state aggregates from 15-minute buckets into daily buckets.

Required arguments
NameTypeDescription
aggStateAggState aggregates created using compact_state_agg
Returns
ColumnTypeDescription
aggStateAggA new state aggregate that combines the input state aggregates
Examples

Combine multiple state aggregates and calculate the duration spent in the START state:

WITH buckets AS (SELECT
time_bucket('1 minute', ts) as dt,
toolkit_experimental.compact_state_agg(ts, state) AS sa
FROM states_test
GROUP BY time_bucket('1 minute', ts))
SELECT toolkit_experimental.duration_in(
'START',
toolkit_experimental.rollup(buckets.sa)
)
FROM buckets;

Keywords

Found an issue on this page?

Report an issue!