How to find where time interval breaks in SQLite3 Database Table

Question

I have SQLite3 Database with one table like this:
Time Val1 Val2 Val3
10:12:03 3.4 4.3 5
10:12:04 3.2 4.1 6
10:12:05 3.3 4.6 5
11:03:19 2.4 5.7 4
11:03:20 2.5 5.2 4
11:03:21 2.7 5.9 5
...

The time range between two rows more than 1 second means the end of the first work interval and the beginning of the second work interval.
So, the first work interval is 10:12:03 - 10:12:05
the second work interval is 11:03:19 - 11:03:21
and so on.

How can I get to know all timestamps where the intervals begin (10:12:03, 11:03:19...) with use of SQL?

GMB GMB · Accepted Answer · 2020-05-26T16:51:10

This is a gaps and islands problem: you want to display rows that are not adjacent to the previous one (that is the beginning of each island).

Here is an approach using window functions:

select t.*
from (
    select 
        t.*,
        lag(time) over(order by time) lag_time
        from mytable t
) t
where
    lag_time is null
    or time >  datetime(lag_time, '+ 1 second')

lag() retrieves the time on the previous row. Then we filter on rows that have a difference greater than one second with the previous row (or for which there is no previous record).

How to find where time interval breaks in SQLite3 Database Table

1 Answers