interpolated_duration_in()

interpolated_duration_in()

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

2. interpolated_duration_in(

4. state [TEXT | BIGINT],

6. tws [StateAgg | TimelineAgg],

8. start TIMESTAMPTZ,

10. interval INTERVAL,

12. prev [StateAgg | TimelineAgg],

14. next [StateAgg | TimelineAgg]

16. ) RETURNS DOUBLE PRECISION

warning

Experimental features could have bugs. They might not be backwards compatible, and could be removed in future releases. Use these features at your own risk, and do not use any experimental features in production.

Required arguments

Name	Type	Description
`state`	`TEXT` or `BIGINT`	State to query
`aggregate`	`StateAgg` or `TimelineAgg`	Previously created state_agg aggregate
`start`	`TIMESTAMPTZ`	The start of the interval which this function should cover (if there is a preceeding point)
`interval`	`INTERVAL`	The length of the interval

Optional arguments

Name	Type	Description
`prev`	`StateAgg` or `TimelineAgg`	The `StateAgg` or `TimelineAgg` 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.
`next`	`StateAgg` or `TimelineAgg`	The `StateAgg` or `TimelineAgg` from the following interval, used to interpolate the value at `start` + `interval`. If `NULL`, the last timestamp in `aggregate` is used as the end of the interval.

Returns

Column	Type	Description
`interpolated_duration_in`	`INTERVAL`	The total time spent in the target state. Displayed as `days`, `hh:mm:ss`, or a combination of the two.

Sample usage

This example creates a simple test table:

2. SET timezone TO 'UTC';

4. CREATE TABLE states(time TIMESTAMPTZ, state TEXT);

6. INSERT INTO states VALUES

8. ('1-1-2020 10:00', 'starting'),

10. ('1-1-2020 10:30', 'running'),

12. ('1-2-2020 16:00', 'error'),

14. ('1-3-2020 18:30', 'starting'),

16. ('1-3-2020 19:30', 'running'),

18. ('1-4-2020 12:00', 'stopping');

You can query this table for the time spent in the running state, like this:

2. SELECT

5. time,

7. toolkit_experimental.interpolated_duration_in(

9. 'running',

11. agg,

13. time,

15. '1 day',

17. LAG(agg) OVER (ORDER BY time),

19. LEAD(agg) OVER (ORDER BY time)

21. ) FROM (

23. SELECT

25. time_bucket('1 day', time) as time,

27. toolkit_experimental.state_agg(time, state) as agg

29. FROM

31. states

33. GROUP BY time_bucket('1 day', time)

35. ) s;

Which gives the result:

2. time          | interpolated_duration_in

5. ------------------------+--------------------------

7. 2020-01-01 00:00:00+00 | 13:30:00

9. 2020-01-02 00:00:00+00 | 16:00:00

11. 2020-01-03 00:00:00+00 | 04:30:00

13. 2020-01-04 00:00:00+00 | 12:00:00

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