duration_in() - Tiger Data

Early access Since 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.

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

The syntax is:

duration_in(
  agg CompactStateAgg,
  state {TEXT | BIGINT}
) RETURNS INTERVAL

Name	Type	Default	Required	Description
agg	CompactStateAgg	-	✔	A state aggregate created with `compact_state_agg`
state	TEXT \| BIGINT	-	✔	The state to query

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.