t-sql Summing differences between timestamps

Question

Hi,

I'm tracking machine state which can be 0,1 and 2, and storing that data in sql table with time_stamp. I have table in sql server with next fields: id(int) time_stamp(datetime) machine_state(int)

Machine state is connected with machine condition:
machine_state =0 -machine stooped
machine_state =1-machine with alarm
machine_state =2-machine running

Now I want to calculate how long machine was in each state in each shift. Shifts are

1. 8:00-17:00
2. 17:00-01:00
3. 01:00-08:00.

My problem is how I can calculate time of each state of machine(sum_time_0, sum_time_1, sum_time_2) and group that times by the shift. I want to calculate time in seconds and then convert to minutes.

To have better picture I did export part of table

EXPORT_TABLE
id    time_stamp          machine_state
1623  6.10.2009 17:09:00  1
1624  6.10.2009 17:17:00  2
1625  6.10.2009 17:17:00  1
1626  6.10.2009 17:17:00  2
1627  6.10.2009 17:18:00  1
1628  6.10.2009 17:18:00  2
1629  6.10.2009 18:04:00  1
1630  6.10.2009 18:06:00  2
1631  6.10.2009 18:07:00  1
1632  6.10.2009 18:12:00  2
1633  6.10.2009 18:28:00  1
1634  6.10.2009 18:28:00  2
1635  6.10.2009 19:16:00  1
1636  6.10.2009 19:21:00  2
1637  6.10.2009 19:49:00  1
1638  6.10.2009 20:23:00  2

Any advice will help. Thanks in advance.

Answer 1

You can do smth like this:

select t1.time_stamp time_start, t2.time_stamp time_finish, t1.machine_state
from EXPORT_TABLE t1, EXPORT_TABLE t2
where t2.time_stamp = (select min(time_stamp) from @table where time_stamp > t1.time_stamp)

This will return you the interval in one row, after that it's easy to calculate cumulative time for each state.

You can also look at this question. It seems to be almost similar to yours.

Answer 2

You can join the next machine state for each row then group by the state and sum the difference in time...

create table #t(id int identity(1,1), ts datetime, ms tinyint);

insert into #t
select '6.10.2009 17:09:00',  1
union select '6.10.2009 17:17:00',  2
union select '6.10.2009 17:17:00',  1
union select '6.10.2009 17:17:00',  2
union select '6.10.2009 17:18:00',  1
union select '6.10.2009 17:18:00',  2
union select '6.10.2009 18:04:00',  1
union select '6.10.2009 18:06:00',  2
union select '6.10.2009 18:07:00',  1
union select '6.10.2009 18:12:00',  2
union select '6.10.2009 18:28:00',  1
union select '6.10.2009 18:28:00',  2
union select '6.10.2009 19:16:00',  1
union select '6.10.2009 19:21:00',  2
union select '6.10.2009 19:49:00',  1
union select '6.10.2009 20:23:00',  2

select
    t.ms,
    sum(datediff(mi, t.ts, tn.ts)) as total_mintues
from
    #t t
    inner join #t tn on
     tn.id = (select top 1 t2.id 
       from #t t2 
       where t2.id > t.id and t2.ms <> t.ms
       order by t2.id)
group by
    t.ms

/*
ms  total_mintues
1   54
2   140
*/

drop table #t

Answer 3

Here's an outline of how I'd do it. I am making some assumptions which may be invalid or not apply to your situation, so I'm not coding everything out.

First, I'd break the problem into chunks: calculate the data for one shift at a time. (I'm guessing you run this once a day, or maybe once a week.)

I would implement this as a stored procedure with two parameters:

• @ShiftDate, specifying the date to be calculated (use the date portion only, ignore any time value)
• @Shift, specifying which shift to analyze (1, 2, 3, as you defined)

Build two "full" datetimes, one for the start of the shift, one for the end. For example, if @ShiftDate = 'Oct 22, 2009' and @Shift = 2, you'd get

• @ShiftStart = 'Oct 22, 2009 17:00:00'
• @ShiftStop = 'Oct 23, 2009 1:00:00'

Create a temp table to hold the subset of the data that we'll be analyzing. Populate it like so:

• Copy over all the data for between @ShiftStart and @ShiftStop
• Do NOT include any data where consecutive (by time) entries have the same state. If any such data exists, discard all but the earliest entry. (It looks like your data is generated this way--but do you want to assume the data will always be good?)
• Add a column for a uniformly incrementing counter (1, 2, 3, etc.). It looks like you've already got this too, but again, you want to be sure here.

Next, check if entries are present for both @ShiftStart and @ShiftStop. If there are no such entries:

• For @ShiftStart, create the entry and set machine_state to whatever the value from the most recent entry before @ShiftStart
• For @ShiftStop, create the entry and set machine_state to, well anything, as we won't reference that value
• In both cases, make sure you correctly configure the counter column (@ShiftStart's counter is one less than the earliest value, @ShiftStops' counter is one greater than the last value)
• (The above is why you make it a temp table. If you can't load these dummy rows, you'll have to use procedural code to walk through the tables, which is the kind of procedural code that bogs down database servers.)

You need these entries to get the data for the time between the start of the shift and the first recorded entry within that shift, and ditto for the end of the shift.

At this point, items are ordered in time, with a uniformly incrementing counter column (1, 2, 3). Assuming all the above, the following query should return the data you're looking for:

SELECT
   et.machine_state
  ,sum(datediff(ss, et.time_stamp, thru.time_stamp))      TotalSeconds
  ,sum(datediff(ss, et.time_stamp, thru.time_stamp)) / 60 TotalMinutes
 from #EXPORT_TABLE et
  inner join #EXPORT_TABLE thru
   on thru.id = et.id + 1
 group by et.machine_state
 order by et.machine_state

Notes:

• This is written for MS SQL Server. Your language syntax may differ.
• I have not tested this code. Any typos were intentionally included so that your final version will be superior to mine.
• #EXPORT_TABLE is the temporary table described above.
• In MS SQL, dividing the sum of an integer by an integer will produce a truncated integer, meaning 59 seconds will turn into 0 minutes. If you need better accuracy, dividing by 60.0 would produce a decimal value.

This is just a framework. I think you'd be able to exapnd this to whatever conditions you have to deal with.

Answer 4

You can use an exclusive join to find the previous row:

select
    State = prev.ms,
    MinutesInState = sum(datediff(mi, prev.ts, cur.ts))
from @t cur
inner join @t prev
    on prev.id < cur.id
left join @t inbetween
    on prev.id < inbetween.id
    and inbetween.id < cur.id
where inbetween.id is null
group by prev.ms

The query then groups by machine state. The result differs from other answers here, I'm curious which one is right!

State  MinutesInState
1      54
2      140

Here's the sample data I used:

declare @t table (id int identity(1,1), ts datetime, ms tinyint);

insert into @t
select '6.10.2009 17:09:00',  1
union select '6.10.2009 17:17:00',  2
union select '6.10.2009 17:17:00',  1
union select '6.10.2009 17:17:00',  2
union select '6.10.2009 17:18:00',  1
union select '6.10.2009 17:18:00',  2
union select '6.10.2009 18:04:00',  1
union select '6.10.2009 18:06:00',  2
union select '6.10.2009 18:07:00',  1
union select '6.10.2009 18:12:00',  2
union select '6.10.2009 18:28:00',  1
union select '6.10.2009 18:28:00',  2
union select '6.10.2009 19:16:00',  1
union select '6.10.2009 19:21:00',  2
union select '6.10.2009 19:49:00',  1
union select '6.10.2009 20:23:00',  2

Answer 5

If you just want quick and dirty, this will do:

select curr.*, prev.*
from EXPORT_TABLE curr
outer apply (
  select top 1 * from EXPORT_TABLE prev
  where curr.time_stamp > prev.time_stamp
  order by time_stamp desc, id desc
  ) prev

And go from there.

But this method, and some of the similar methods on this page involving a non-equijoin, will not scale well with volume. To handle a high volume of data, we must use different techniques.

Your id appears sequential. Is it? This can be useful. If not, we should create one.

if object_id('tempdb..#pass1') is not null drop table #pass1
create table #pass1 (
  id            int
, time_stamp    smalldatetime
, machine_state tinyint
, seqno         int primary key -- this is important
)

insert #pass1 
select 
  id
, time_stamp
, machine_state
, seqno = row_number() over (order by time_stamp, id)
from EXPORT_TABLE

Once we have a sequential id, we can equi-join on it:

if object_id('tempdb..#pass2') is not null drop table #pass2
create table #pass2 (
  id              int
, time_stamp      smalldatetime
, machine_state   tinyint
, seqno           int primary key
, time_stamp_prev smalldatetime
)
insert #pass2
select 
  id
, time_stamp
, machine_state
, seqno
, time_stamp_prev = b.time_stamp
from #pass1 a
left join #pass1 b on a.seqno = b.seqno + 1

From here, your query should just about write itself. Look out for machine states that overlap a shift, though.

This method, though it looks expensive, will scale well with volume. You order the data once, and join once. If the id is sequential, you can skip the first step, make sure there is a clustered primary key on id, and join on id rather than seqno.

If you have a really high volume of data, you do this instead:

if object_id('tempdb..#export_table') is not null drop table #export_table
create table #pass1 (
  id              int
, time_stamp      smalldatetime
, machine_state   tinyint
, seqno           int primary key -- ensures proper ordering for the UPDATE
, time_stamp_prev smalldatetime
)

insert #export_table (
  id
, time_stamp
, machine_state
, seqno
)    
select 
  id
, time_stamp
, machine_state
, seqno = row_number() over (order by time_stamp, id)
from EXPORT_TABLE

-- do some magic
declare @time_stamp smalldatetime
update #export_table set
  time_stamp_prev = @time_stamp
, @time_stamp = time_stamp

This will out-perform all other methods. And if your id is in the right order (it does not have to be sequential, just in the right order), you can skip the first step and define a clustered index on id instead, if it's not already there.

Answer 6

Hi, thanks on help. I surprised how detail is the answer. I will tests you solution and inform you about result. Again I'm very surprised with detail answer.

I did test first part(to sum time of machine state 0, 1 i 2) and this is OK. Now I will test rest part of the answer.

Biggest problem for me was time splitting during shift transition. example: '6.10.2009 16:30:00', 1 '6.10.2009 17:30:00', 2 '6.10.2009 19:16:00', 1

In time between 16:30 and 17:00 machine was in state 1 and that time I have to add to shift 1, and time between 17:00 and 17:30 machine was in state 1 and that time I have to add to shift 2.

But first I will go through you answer to see did you already make solution for this.

thanks again

Answer 7

CREATE PROCEDURE dbo.final  @shiftdate datetime, @shift int

AS
BEGIN

DECLARE
     @shiftstart  as datetime ,
     @shiftstop as datetime,
     @date_m as varchar(33),
    @timestart as char(8),
    @smjena as int,
     @ms_prev as int,
     @t_rad as int,
     @t_stop as int,
     @t_alarm as int

if @shift = 1
begin
     set @timestart = '08:00:00'
     set @smjena=9
end
if @shift = 2
begin
     set @timestart = '17:00:00'
     set @smjena=8
end
if @shift = 3
begin
    set @timestart = '01:00:00'
     set @smjena=7
end


SELECT @date_m = convert(varchar,  @shiftdate, 104) + ' ' + convert(varchar,  @timestart, 114)
set @shiftstart = convert(datetime,@date_m,104)



select @shiftstop = dateadd(hh,@smjena,@shiftstart)


create table #t(id int identity(1,1), ts datetime, ms tinyint);
insert #t select time_stamp, stanje_stroja from perini where perini.time_stamp between @shiftstart and @shiftstop order by perini.time_stamp



if (select count(#t.id)  from #t where #t.ts=@shiftstart)= 0
BEGIN
if (select count(perini.id) from perini where time_stamp < @shiftstart) > 0
  begin
  set @ms_prev = (select top 1 stanje_stroja from perini where time_stamp<@shiftstart order by time_stamp asc)
  insert #t values (@shiftstart,@ms_prev)
end
end


if (select count(#t.id)  from #t where #t.ts=@shiftstop)= 0
BEGIN
if (select count(perini.id) from perini where time_stamp > @shiftstop) > 0
 begin
 set @ms_prev = (select top 1 stanje_stroja from perini where time_stamp>@shiftstop order by time_stamp asc)
 insert #t values (@shiftstop,@ms_prev)
end
end

select * into #t1 from #t where 1=2
insert into #t1 select ts, ms from #t order by ts


create table #t3(stanje int, trajanje int)

insert into #t3 select a.ms as stanje, convert(int,sum(datediff(ss,b.ts, a.ts))/60) as trajanje  from
#t1 a left join #t1 b on a.id = b.id + 1
group by a.ms



set @t_rad = (select trajanje from #t3 where stanje = 2)
set @t_alarm = (select trajanje from #t3 where stanje = 1)
set @t_stop = (select trajanje from #t3 where stanje = 0)

insert into perini_smjene_new (smjena,t_rad, t_stop, t_alarm, time_stamp) values (@shift,@t_rad,@t_stop, @t_alarm, convert(datetime,  @shiftdate, 103))




select * from #t3


END