duration_in() - Tiger Data

Since 1.15.0 Calculate the total time spent in a 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 duration_in(
  state_agg(time, state),
  'running'
) FROM states;

Returns:

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

Arguments

The syntax is:

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

Name	Type	Default	Required	Description
agg	StateAgg	-	✔	A state aggregate created with `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.