Is there a hyperfunction that somehow can be used to retrieve state time? In this case, I have a events table for machine states. It is supposed to be a insert only table, so when machine A changes to state 2 a new row is created with timestamp, machine id and state value, regardless of what was the previous state. What is needed in this case is to get event duration. For example, for the row mentioned above, it would be the timestamp from that to row until the timestamp to the next row for the same machine ID but with a different state value (state repetition should be ignored). We can do that with lead and lag function but I wonder if a hyperfunction is already solving this. Another different case is to get the state time per hour or day, for each machine.
I think state_agg can help me, with its related functions.
And to group by machine_id, I simply used group by (I was not expecting it to work. Awesome!)
select duration_in(state_agg(“Timestamp”, status),status), status, machine_id
FROM public.events_ts
–where machine_id = 15
–and
where “Timestamp” >= now() - interval ‘1 day’
group by status, machine_id
Well, adding the status inside the duration_in function did not work with group by. It does not bring an error, but the result is wrong.