How to find the max time difference in PostgreSQL that a value stayed on the same state?

Question

I am working for a university project and this came up to me:

I have a table like this:

And I want to get the max duration that an actuator was on a state. For example, cool0 Was for 18 minutes.

The result table should look like this:

NAME         COOL0   
State        False
Duration     18

Please read [Why not upload images of code on SO when asking a question](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) and [edit] your question to include code and data as text, not images. — Nick, May 24 '20 at 02:48
Please don't SHOUT — we can read mixed-case text more easily. — Jonathan Leffler, May 24 '20 at 02:49
Just tag the DBMS you're actually using, not a random array of them. I assume, from the title, that Postgres is the actual one. — sticky bit, May 24 '20 at 02:49
The visible data does not support your contention that `COOL0` was on for 18 minutes. Unless my eyes are deceiving me, the 4 records for `COOL0` all have the same actuator time. In fact, the visible records are identical. — Jonathan Leffler, May 24 '20 at 03:00

score 1 · Accepted Answer · answered May 24 '20 at 02:52

This is a gaps and islands problem. Your data is a bit hard to follow, but I think:

select actuator, state, min(actuator_time), max(actuator_time)
from (select t.*,
             row_number() over (partition by actuator order by actuator_time) as seqnum,
             row_number() over (partition by actuator, state order by actuator_time) as seqnum_s
      from t
     ) t
group by actuator, (seqnum- seqnum_s)

For the maximum per actuator, use distinct on:

select distinct on (actuator) actuator, state, min(actuator_time), max(actuator_time)
from (select t.*,
             row_number() over (partition by actuator order by actuator_time) as seqnum,
             row_number() over (partition by actuator, state order by actuator_time) as seqnum_s
      from t
     ) t
group by actuator, (seqnum- seqnum_s)
order by actuator, max(actuator_time) - min(actuator_time) desc;

How to find the max time difference in PostgreSQL that a value stayed on the same state?

1 Answers1