How to find the difference between two timestamped rows whenever value changes between rows in MySQL

Question

My Data Set looks like this:

The Output given in column D is derived as follows:

1. Output against index 2 : TimeStamp in Index 3 - TimeStamp in Index 2

2. Output against index 6 : TimeStamp in Index 10 - TimeStamp in Index 6

3. Output against index 12 : TimeStamp in Index 15 - TimeStamp in Index 12

DataSet MySQL V2012

create table #temp11 (Index# int, TimeStamp# Datetime, Alarm int)

insert into #temp11 values
(1, '10/6/2019 00:08:01', 0),
(2, '10/6/2019 00:08:13'    ,1),
(3, '10/6/2019 00:08:15'    ,1),
(4, '10/6/2019 00:10:47'    ,0),
(5, '10/6/2019 00:10:58'    ,0),
(6, '10/6/2019 00:10:59'    ,1),
(7, '10/6/2019 00:11:00'    ,1),
(8, '10/6/2019 00:11:01'    ,1),
(9, '10/6/2019 00:11:02'    ,1),
(10, '10/6/2019 00:11:03'   ,1),
(11, '10/6/2019 00:11:04'   ,0),
(12, '10/6/2019 00:11:05'   ,1),
(13, '10/6/2019 00:11:06'   ,1),
(14, '10/6/2019 00:11:07'   ,1),
(15,'10/6/2019 00:11:15'    ,1)

TIA

Answer 1

This is a variant of the gaps-and-islands problem. Here is one way to solve it using window functions (available in MySQL 8.0):

select 
    t.*,
    case when 
        alarm = 1 
        and row_number() over(partition by alarm, rn1 - rn2 order by TimeStamp) = 1
    then timestampdiff(
            second,
            min(TimeStamp) over(partition by alarm, rn1 - rn2),
            max(TimeStamp) over(partition by alarm, rn1 - rn2)
        )
    end out
from (
    select
        t.*,
        row_number() over(order by TimeStamp) rn1,
        row_number() over(partition by alarm order by TimeStamp) rn2
    from mytable t
) t

The inner query ranks record in the whole table and in partition of records sharing the same alarm. The difference between the ranks gives you the group each record belong to.

Then, the outer query identifies the first record in each group with alarm = 1, and computes the difference between the first and last record in the group, in seconds.