TimescaleDB – how get timestamp differences between rows?

Say you need to know how long something was ‘active’ in a time range – (with timestamp in minutes, as an example) –

timestamp, state
2.1, unavailable
3.5, active
6.8, wait
7.3, unavailable
9.3, active

For a 0-15min bucket, the answer would be 6.8-3.5 + 15-9.3 = 9.0mins. (ie the first active state lasts 6.8-3.5mins, the next one goes from 9.3mins to the 15min barrier).

How would you do something like this in TimescaleDB?

Answer

Your data does not correspond to the implied data type (3.5 is not a timestamp). However, it appears you are trying to get the sum of the difference between consecutive entries. If so use the lead function to get the next value (coalesce with 15 for the last entry) then sum the difference for active state. (see demo)

select sum(nts-ts) 
  from ( select ts ,coalesce(lead(ts) over( order by ts),15) nts, state from t) s
 where state = 'active'