t-sql 汇总时间戳之间的差异

2021-09-10 00:00:00 sql tsql sql-server

我正在跟踪机器状态，它可以是 0,1 和 2，并使用time_stamp将该数据存储在sql表中.我在 sql server 中有下一个字段的表:身份证(整数)时间戳(日期时间)machine_state(int)

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 =0 -machine 弯腰
machine_state =1-带警报的机器
machine_state =2-机器运行

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.

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

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.

推荐答案

您可以为每一行加入下一个机器状态，然后按状态分组并求和时间差...

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

相关文章