MySQL query to find the longest sequence of value based on date

Question

I want to find the longest sequence of a given value based on date column, for example given the table:

+-------+-------------------+
|value  |timestamp          |
+-------+-------------------+
|1      |2021-02-20 13:31:21|
|0      |2021-02-20 13:31:58|
|1      |2021-02-20 13:32:00|
|1      |2021-02-20 13:33:24|
|1      |2021-02-20 13:34:12|
|0      |2021-02-20 13:36:51|

for value "1" the longest sequence is 2 minutes and 12 seconds long, how can this be done?

Hope someone will be able to help! Thanks!

Answer 1

You can assign a group by counting the number of 0 values cumulatively. Then just aggregate to see all the groups:

select min(timestamp), max(timestamp)
from (select t.*,
             sum(value = 0) over (order by timestamp) as grp
      from t
     ) t
where value = 1
group by grp;

To calculate the difference and take the longest period:

select min(timestamp), max(timestamp),
       second_to_time(to_seconds(max(timestamp)) - to_seconds(min(timetamp)))
from (select t.*,
             sum(value = 0) over (order by timestamp) as grp
      from t
     ) t
where value = 1
group by grp
order by to_seconds(max(timestamp)) - to_seconds(min(timetamp)) desc
limit 1;