SQL - Find series of timestamps with small gaps, Sql Server

SQL - Find series of timestamps with small gaps

create table T ([timestamp] datetime);
    
insert into T ([timestamp])
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');

select 'Version 1' as "Query";
with Boundaries as (
    select
        "timestamp" as Stamp,
        coalesce(
            case when datediff(second, prev_timestamp, "timestamp") >= 1800 
                then 1 else 0 end,
            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 (order by Stamp) as BlockNum
    from Boundaries
)
select min(Stamp) as "from", max(Stamp) as "to"
from Blocks
group by BlockNum;

select 'Version 2' as "Query";
with Boundaries as (
    select
        "timestamp" as Stamp,
        coalesce(
            case when datediff(second, prev_timestamp, "timestamp") >= 1800 
                then 1 else 0 end,
            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, (select sum(b2.IsBoundary) from Boundaries b2 where b2.Stamp <= b.Stamp) as BlockNum
    from Boundaries b
)
select min(Stamp) as "from", max(Stamp) as "to"
from Blocks
group by BlockNum;


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development