duration_in()

Calculate the total time spent in a given state from a state aggregate

Early access 1.5.0

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

duration_in(
  agg StateAgg,
  state {TEXT | BIGINT}
  [, start TIMESTAMPTZ]
  [, interval INTERVAL]
) RETURNS INTERVAL

Arguments

Name	Type	Default	Required	Description
agg	StateAgg	-	✔	A state aggregate created with compact_state_agg
state	TEXT | BIGINT	-	✔	The state to query
start	TIMESTAMPTZ	-		If specified, only the time in the state after this time is returned
interval	INTERVAL	-		If specified, only the time in the state from the start time to the end of the interval is returned

Returns

Column	Type	Description
duration_in	INTERVAL	The time spent in the given state. Displayed in days, hh:mm:ss, or a combination of the two

Samples

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;

Returns:

duration_in
---------------
3 days 22:00:00