Intervals duration in time buckets

1

Hi,

I’ve got a table, let’s call it “DevStates” that contains states and durations expressed as start/end timestamps for devices, e.g.

DeviceId | State | Start | End
1 | ‘STATE-X’ | 2024-06-09 01:32:41.63+00 | 2024-06-09 01:33:03.913+00
2 | ‘STATE-Y’ | 2024-06-09 01:35:49.43+00 | 2024-06-09 01:39:35.577+00

and so on. The “holes” should be filled with the meta-state ‘IDLE’.
I should create a continuous aggregate that tells me, in hourly time buckets, how many minutes have been spent in any state.
Now, my first thought was:

  1. transform the intervals in state changes:
    SELECT DeviceId, State, Start AS TS FROM DevStates
    UNION ALL
    SELECT DeviceId, ‘IDLE’ AS State, End AS TS FROM DevStates

  2. apply duration_in, time_bucket and create a continuous aggregate

I just started working on this solution, so I’m not even sure that it could work, and at the same time I was thinking if this is the right and most efficient approach.

Thanks a lot,

Mattia.