How to query SQL Server database to get a sum of time differences but only if they are less than 2 min different

Question

I have the following queried data which is:

SELECT * 
FROM MYTABLE 
WHERE tagid = '65'

Output:

   tagid    floatvalue  t_stamp
   -------------------------------
    65  25.51477051 1455897455214
    65  35.71407318 1455897485215
    65  36.05856323 1455897515215
    65  35.72781372 1455897545214
    65  35.99771118 1455897575215
    65  35.87993622 1455897605215
    65  36.23326111 1455897665215
    65  35.8652153  1455897695215
    65  35.73075485 1455897725216
    65  35.94765472 1455897785216
    65  36.36379242 1455897815217
    65  35.93685913 1455897845216
    65  36.64154816 1455898025219
    65  36.44329071 1455898055218
    65  36.07524872 1455898085219
    65  36.40992355 1455898115217
    65  38.13336182 1455898145217

The t_stamp column is a big int of Unix time * 1000.

This data is logging every ~30 sec (30,000) if the machine is running. I am attempting to query this for the sum of the time differences if they are less than two minutes (120,000) from the row before it. If it is greater than two minutes then I assume the machine was off and that row would be a new start time for the next sum.

My goal here is to get a sum of the total run time using the time stamps.

I am at a complete loss where to start on this. I have had a hard time attempting to make this explanation even make sense to me, much less you guys and apologize if I've made a mess of it.

Can you show what you've tried that brings you close to the solution? — SQLMason, Aug 15 '16 at 20:08
expected output would be tagid and sum of all blocks of "on time". Dan, I am at a complete loss where to start. Summing the time differences is not a problem but determining what is a block of time for the difference I don't know other than it will be less than 120,000ms — Xgrunt24, Aug 16 '16 at 11:07

shawnt00 · Accepted Answer · 2016-08-28T14:29:17.793

If you don't have lag() there other ways to get the previous timestamp.

;with step1 as (
    select
        t_stamp,
        case
            when t_stamp - lag(t_stamp) over (partition by tagid order by t_stamp) > 120000
            then 1 else 0
        end as brk
    from mytable
), step2 as (
    select t_stamp, sum(brk) over (partition by tagid order by t_stamp) as grp,
    from step1
)
select
    grp, min(t_stamp) as start_time, max(t_stamp) as end_time,
    max(t_stamp) - min(t_stamp) as total_time
from step2
group by tagid, grp;

This is a fairly typical problem under the "Gaps and Islands" heading. You'll find lots of examples similar to your question.

By total coincidence I spotted this Oracle article today from more than a decade ago. It uses a slightly different approach that you might find interesting.

Edit:

Here's a quick breakdown of the logic.

Step 1 compares each stamp to the previous value, in order of time, by using lag. When it finds a gap greater than the threshold it gets marked as a 1. All others get zeroes (null would work too.) This effectively marks the beginning of a new block aka "grp".

Step 2 computes a running total in the same order. So a running total only changes each time a new block starts and thus every row within the block gets the same value. This value is used in group by and the time difference is computed then as the span between the minimum and maximum timestamps.

This appears to be what I was wanting to try and do. I will have to dig into the data and verify. I will also need to read up on lag() and work my way through this query and try to understand it. Thanks Shawnt and I will post as soon as I know something. — Xgrunt24, Aug 16 '16 at 11:24
@Xgrunt24 I've tweak the query to produce one row per `tagid` since a comment suggests you need that. — shawnt00, Aug 16 '16 at 16:38
After verifying the data this is in fact giving exactly what I needed. Now to figure out exactly what this query is doing... Thanks again for your time and help! — Xgrunt24, Aug 16 '16 at 17:43

score 0 · Answer 2 · answered Aug 15 '16 at 20:28

Try a cursor.

declare @sum bigint
declare @t_stamp bigint
declare @last bigint
declare @diff bigint
select @sum = 0
declare MyCursor cursor for select t_stamp from mytable where tagid=65
open MyCursor


fetch next from MyCursor into @t_stamp 
while @@fetch_Status = 0
begin

   if (not (@last is null))
   begin
       select @diff = @t_stamp - @last

       if (@diff < 120000)
       begin
           select @sum = @sum + @diff
       end 
   end
   select @last = @t_stamp

   fetch next from MyCursor into @t_stamp 
end
close MyCursor
deallocate MyCursor

print @sum

How to query SQL Server database to get a sum of time differences but only if they are less than 2 min different

2 Answers2