問題描述
我正在跟蹤機器狀態，它可以是 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
- 8:00-17:00
- 17:00-01:00
- 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
這篇關于t-sql 匯總時間戳之間的差異的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！