t-sql和时间戳之间的差异。

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)

我正在跟踪可以是0、1和2的机器状态，并使用time_stamp将数据存储在sql表中。我在sql server中有一个表，下一个字段是: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

机器状态与机器状态相连接:machine_state =0 -machine stoe_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. 8:00-17:00
3. 17:00-01:00
4. 17:00-01:00
5. 01:00-08:00.
6. 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.

我的问题是如何计算机器的每个状态的时间(sum_time_0、sum_time_1、sum_time_2)并按移位对其进行分组。我想计算时间，以秒为单位，然后转换成分钟。

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.

任何建议将帮助。提前谢谢。

7 个解决方案

#1

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

#2

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)
• @ShiftDate，指定要计算的日期(仅使用日期部分，忽略任何时间值)
• @Shift, specifying which shift to analyze (1, 2, 3, as you defined)
• @Shift，指定要分析的偏移(1、2、3，如您所定义)

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

构建两个“完整”的日期时间，一个用于换班的开始，一个用于结束。例如，如果@ShiftDate = 'Oct 22, 2009'和@Shift = 2，你会得到。

• @ShiftStart = 'Oct 22, 2009 17:00:00'
• @ShiftStart = 'Oct 22, 2009 17:00:00'
• @ShiftStop = 'Oct 23, 2009 1:00:00'
• @ShiftStop = ' 2009年10月23日

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
• 复制@ShiftStart和@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.
• 为一个均匀递增的计数器(1,2,3，等等)添加一个列。看起来你已经有这个了，但你还是要确定。

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

接下来，检查@ShiftStart和@ShiftStop是否都有条目。如果没有这样的条目:

• For @ShiftStart, create the entry and set machine_state to whatever the value from the most recent entry before @ShiftStart
• 对于@ShiftStart，创建条目并将machine_state设置为@ShiftStart之前最近的条目的值
• For @ShiftStop, create the entry and set machine_state to, well anything, as we won't reference that value
• 对于@ShiftStop，创建条目并将machine_state设置为任何值，因为我们不会引用该值
• 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)
• 在这两种情况下，确保正确配置计数器列(@ShiftStart的计数器比最早的值少1，@ShiftStops'计数器比最后一个值大1)
• (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:

此时，项是按时间顺序排列的，具有一致递增的计数器列(1,2,3)。

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.
• 这是为MS SQL Server编写的。您的语言语法可能有所不同。
• 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.

• EXPORT_TABLE是上面描述的临时表。
• 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.
• 在MS SQL中，将一个整数的和除以一个整数将产生一个被截断的整数，这意味着59秒将变成0分钟。如果您需要更好的精度，除以60.0将产生一个十进制值。

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

这只是一个框架。我认为你能在任何情况下都能应付得来。

#3

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

#4

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:

一旦我们有了一个连续的id，我们就可以对它进行等联:

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.

这种方法虽然看起来很昂贵，但会随着体积的增大而扩展。您可以一次订购数据，然后加入一次。如果id是连续的，您可以跳过第一步，确保id上有集群主键，并在id上连接，而不是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.

这将优于其他所有方法。如果您的id的顺序是正确的(它不必是顺序的，只是顺序正确)，那么您可以跳过第一步，在id上定义一个聚集索引，如果它还不存在的话。

#5

You can do smth like this:

你可以这样做smth:

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.

你也可以看看这个问题。看起来和你的差不多。

#6

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.

我测试了第一部分(对机器状态0和1 i2的时间求和)，这没问题。现在我将测试其余部分的答案。

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

对我来说，最大的问题是换班期间的时间分配。例:'6.10.2009 16:30:00'，1 '6.10.2009 17:30:00'，2 '6.10.2009 19:16: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.

在16:30到17:00之间，机器处于状态1，我需要增加shift 1, 17:00到17:30之间，机器处于状态1，我需要增加shift 2。

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

但是首先我要讲一下你们的答案，看看你们已经为这个做了解了吗。

thanks again

再次感谢

#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