CODEWITHSUNDEEP
sqlsql-servertimestamp
Agni
Agni

Reputation: 135

SQL query to compare rows satisfying a time stamp condition

I have a data in the following format.

Column1     Column2     Column3         TimeStamp
   10           20         30       2017-04-25 14:15:00.000
   12           30         40       2017-04-25 14:15:15.000
   55           54         89       2017-04-25 14:15:30.000
   66           78         11       2017-04-25 14:15:45.000
   12           30         40       2017-04-25 14:16:00.000
   55           54         89       2017-04-25 14:16:15.000
   66           78         11       2017-04-25 14:16:30.000

The time stamp is in the format

yyyy-mm-dd hh:mm:ss

The data is logged for every 15 seconds. Hence the difference between time stamps of any consecutive two rows should be 15 seconds. I'm interested in querying the rows whose time difference is let's say for example 20 seconds and between TimeStamps 2017-04-25 14:15:00 and 2017-04-25 14:15:30 respectively.

I started with the following code and its taking forever to finish querying.

SELECT
  *
FROM
  dbo.Temp2 AS t1
WHERE
  EXISTS
  (
    SELECT
      *
    FROM
      dbo.Temp2 AS t2
    WHERE
      t2.TimeStamp - t1.TimeStamp = 20      
  )
  ;

I'm using MS SQL Server 2017.

Any suggestions, ideas would be really helpful.

Thank you in advance.

Upvotes: 1

Views: 1500

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521209

The lead/lag analytic functions might be helpful here:

WITH cte AS (
    SELECT *,
        DATEDIFF(s, TimeStamp, LEAD(TimeStamp, 1) OVER (ORDER BY TimeStamp)) AS diff
    FROM Temp2
    WHERE TimeStamp BETWEEN '2017-04-25 14:15:00' AND '2017-04-25 14:30:00'
)

SELECT *
FROM cte
WHERE diff >= 20;

Demo

In the above demo I had to doctor your sample data slightly to get anything to return, because your timestamp range actually has no values such that the next leading value is 20 seconds apart.

Upvotes: 1

S3S
S3S

Reputation: 25112

Using LEAD and LAG. I also changed your sample data to actually have a gap longer than 15 seconds. This will return both rows for which the gap occurs. In this case, it's the two commented below.

create table #MyTable (C1 int, C2 int, C3 int, TimeStamps datetime)
insert into #MyTable
values
(10,20,30,'2017-04-25 14:15:00.000'),
(12,30,40,'2017-04-25 14:15:15.000'),
(55,54,89,'2017-04-25 14:15:30.000'),
(66,78,11,'2017-04-25 14:15:45.000'), --this row will also be returned
(12,30,40,'2017-04-25 14:16:15.000'), --changed minutes here. This is the only row > 15 second difference than the previous, ordered by TimeStamps
(55,54,89,'2017-04-25 14:16:30.000'),
(66,78,11,'2017-04-25 14:16:45.000')

;with cte as(
select
    *
    ,KeepMe = case when datediff(second,TimeStamps,lead(TimeStamps) over(order by TimeStamps)) > 20  or datediff(second,TimeStamps,lag(TimeStamps) over(order by TimeStamps)) < -20 then 1 else 0 end
from 
    #MyTable)

select
    C1
    ,C2
    ,C3
    ,TimeStamps
from cte
where KeepMe = 1

drop table #MyTable

Upvotes: 2

Related Questions