Detect Gaps Over 30 Min In Timestamp Column

I have read up on and attempted using the standard method of gaps and island detection in a series with no success because I need to be able to ignore any gaps less than 30 minutes

Solution 1:

Easy solution using common table expression. Compare with cursor performance if you have at least 1000 rows.

createtable #tmp (Dt datetime)

insertinto #tmp values 
('2015-07-15 15:01:21'),
('2015-07-15 15:17:44'), 
('2015-07-15 15:17:53'), 
('2015-07-15 15:18:34'), 
('2015-07-15 15:21:41'), 
('2015-07-15 15:58:12'), 
('2015-07-15 15:59:12'), 
('2015-07-15 16:05:12'),
('2015-07-15 17:02:12')

;with tbl as (
select dt, row_number() over(orderby dt) rn
from #tmp
)
select t1.dt [from],t2.dt [to], datediff(minute,t1.dt,t2.dt) gap
from tbl t1 
innerjoin tbl t2 on t1.rn+1= t2.rn
where datediff(minute,t1.dt,t2.dt) >30

Solution 2:

An approach like the one below would seem to fit the bill assuming the performance is okay. I used the sys.all_tables catalog view to simulate a log table like the one in your example. You can vary the first argument to datediff to get different results.

WITH [Sequenced_Entries] AS
(
    SELECT
        row_number() OVER(ORDERBY [modify_date] ASC) AS'sequence',
        [modify_date] AS'event_date'FROM
        sys.all_objects
)
SELECT
    f.[sequence] AS'from_event_sequence',
    f.[event_date] AS'from_event_date',
    t.[sequence] AS'to_event_sequence',
    t.[event_date] AS'to_event_date'FROM
    [Sequenced_Entries] AS f
        INNER JOIN
    [Sequenced_Entries] AS t
        ON (f.[sequence] = t.[sequence] - 1)
WHERE
    datediff(second, f.[event_date], t.[event_date]) < 30ORDERBY
    f.[sequence] ASC

Don't forget the statement before this one in the batch must be terminated with a ; or this statement must start with one.

Solution 3:

;with Boundaries as (
    select"timestamp"as Stamp,
        coalesce(
            casewhen datediff(second, prev_timestamp, "timestamp") >= 1800then1else0end,
            1
        ) as IsBoundary
    from
        T t cross apply (
            select max(t2."timestamp") as prev_timestamp from T t2
            where t2."timestamp" < t."timestamp"
        ) as n
), Blocks as (
    select Stamp, sum(IsBoundary) over (orderby Stamp) as BlockNum
    from Boundaries
)
select min(Stamp) as"from", max(Stamp) as"to"from Blocks
groupby BlockNum

Remember that datediff() just counts interval boundaries so you need to take some care with the time difference. I'm using 1800 seconds here.

If you had a later edition of SQL Server you could use lead()/lag() to look for the gaps. But instead of an inner join the cross apply will hopefully be significantly faster.

I think your title "find gaps over 30 minutes" as well as a comment to "ignore gaps less than 30 minutes" has misled some posters who thought that you were only seeking to find the rows that bounded a gap of 30 minutes. This is the only one that addresses the problem according to your desired output. (Test here.)

Instead of using analytic sum() over (order by...) it's a simple matter of replacing it with a scalar subquery.

...
), Blocks as (select
      Stamp,
      (select sum(b2.IsBoundary) from Boundaries b2 where b2.Stamp <= b.Stamp) as BlockNum
    from Boundaries b
) ...

Solution 4:

;with cc as (
  select Dt 
    , (select top 1 Dt from #tmp where Dt > ot.Dt orderby Dt) as NextDt
  from #tmp ot
)
select Dt AS [FROM], NextDt AS [TO]
from cc
where DATEDIFF(minute, Dt, NextDt) >= 30orderby Dt

Test

createtable #tmp (Dt datetime)

insertinto #tmp values 
('2015-07-15 15:01:21'),
('2015-07-15 15:17:44'), 
('2015-07-15 15:17:53'), 
('2015-07-15 15:18:34'), 
('2015-07-15 15:21:41'), 
('2015-07-15 15:58:12'), 
('2015-07-15 15:59:12'), 
('2015-07-15 16:05:12'),
('2015-07-15 17:02:12')

Result

FROMTO2015-07-15 15:21:41.000        2015-07-15 15:58:12.0002015-07-15 16:05:12.000        2015-07-15 17:02:12.000

_{If a post answers your question, please Mark it as answer}