timescaledb or postgresql aggregation by getting time difference between two events

Question

I collect device ON/OFF events in a timeseries table. Following is a sample data.

time_stamp	state
2023-10-04 10:05:53	0
2023-10-04 10:15:58	1
2023-10-04 10:30:59	0
2023-10-04 10:40:00	1
2023-10-04 10:55:01	0
2023-10-04 11:05:03	1
2023-10-04 11:35:36	0
2023-10-04 11:40:39	1
2023-10-04 11:55:07	0

I need to aggregate how much time the state remained 1 within each hour. So for hour 10 in the sample below:

• 10:00 to 10:05 - ON = 5 mins *it was turned on before 10:00 but we only count from top of the hour
• 10:05 to 10:15 - OFF = 10 mins
• 10:15 to 10:30 - ON = 15 mins
• 10:30 to 10:40 - OFF = 10 mins
• 10:40 to 10:55 - ON = 15 mins
• 10:55 - 11:00 - OFF = 5 mins

So, out of 60 mins between 10:00 and 11:00, it was ON for 5 + 15 + 15 = 35 mins and OFF for 25 mins.

And similarly for the hour of 11:00 to 12:00, it is ON for 45 mins.

The outcome of the aggregation using the data sample.

time_stamp	minutesON
2023-10-04 10:00:00	35
2023-10-04 11:00:00	45

The question is how can I achieve this using a postgresql query that I can use to write a continuous aggregate in timescale db.

Answer 1

looks like you are looking for this

SELECT measure_id,
    time_bucket('15 min'::interval, ts) as bucket,
    delta(
        counter_agg(ts, val)
    )
FROM example
GROUP BY measure_id, time_bucket('15 min'::interval, ts);

Answer 2

Also, the state_agg allow you to collect interpolated duration in between states.