Search code examples
sqlsqlitegaps-and-islands

Detect gaps between dates in SQLite

I have a SQLite table with the following structure:

enter image description here

As you can see the sampleDate column has a new row every 1 minute, but sometimes the process stops and there are "time gaps" as in the underlined example. I need to find these gaps with a SQL query as this table is huge.

A posible expected result output could be:

2023-03-20 12:56:27
2023-03-29 10:46:46

This could represent that there is a gap between these two dates.

Solution

  • You can use LEAD() function to retrieve the next date then calculate the difference to retrieve gaps :

    with cte as (
  select *, 
          lead(sampleDate, 1, sampleDate) over (order by sampleDate) as nextDate,
          CAST(strftime('%M', lead(sampleDate, 1, sampleDate) over (order by sampleDate)) as integer) 
          - CAST(strftime('%M', sampleDate) as integer) as diff
  from mytable
)
select sampleDate, nextDate
from cte
where diff > 1