设备运行时间

有这么几条设备运行记录

设备1 ： 开机2015-01-01 08:00:00  关机2015-01-01 12:00:00
设备1：开机2015-01-01 14:00:00 关机2015-01-01 19:00:00

设备1： 开机2015-01-02 08:00:00 关机2015-01-02  20:00:00

设备1：开机2015-01-03 08:00:00 关机2015-01-06 12:00:00

求设备1在2015-01-01日的运行时长？
求设备1在2015-01-02日的运行时长？
求设备1在2015-01-05日的运行时长？

上三个问题分别代表的是设备运行的不同种情况，可能一天开关多次，可能一天开关一次，可能连续运行几天，等等

怎么写一个函数方法把这些情况都分析清楚，准确的把设备在一天内的运行时长计算出来？？

小弟想了很久都没什么好办法，各路大神求救

5 个解决方案

#1

如果你这东西在数据库里
直接
sum(关机时间-开机时间)不就得了,然后按日期groupby

#2

如果不是数据库的数据
你也可以LINQ,支持sum,groupby
或者自己循环遍历判断累加

#3

引用 1 楼 Z65443344 的回复:

如果你这东西在数据库里
直接
sum(关机时间-开机时间)不就得了,然后按日期groupby

大侠，比如说：
设备1：开机2015-01-03 08:00:00 关机2015-01-06 12:00:00

如果用    关机时间-开机时间     那么就是3号到6号的运行时间，
但是我要求的只是5号这一天的运行时间，

按照您的说法，还是不正确的

#4

with
cte1 as  --存放每天的开始和结束时间
(
    select convert(datetime, '2015-01-01 00:00:00') BTime, convert(datetime, '2015-01-02 00:00:00') ETime union all
    select convert(datetime, '2015-01-02 00:00:00') BTime, convert(datetime, '2015-01-03 00:00:00') ETime union all
    select convert(datetime, '2015-01-03 00:00:00') BTime, convert(datetime, '2015-01-04 00:00:00') ETime union all
    select convert(datetime, '2015-01-04 00:00:00') BTime, convert(datetime, '2015-01-05 00:00:00') ETime union all
    select convert(datetime, '2015-01-05 00:00:00') BTime, convert(datetime, '2015-01-06 00:00:00') ETime union all
    select convert(datetime, '2015-01-06 00:00:00') BTime, convert(datetime, '2015-01-07 00:00:00') ETime union all
    select convert(datetime, '2015-01-07 00:00:00') BTime, convert(datetime, '2015-01-08 00:00:00') ETime union all
    select convert(datetime, '2015-01-08 00:00:00') BTime, convert(datetime, '2015-01-09 00:00:00') ETime 
),
cte2 as    --存放设备开机和关机时间
(
    select convert(datetime, '2015-01-01 08:00:00') BeginTime, convert(datetime, '2015-01-01 12:00:00') EndTime union all
    select convert(datetime, '2015-01-01 14:00:00') BeginTime, convert(datetime, '2015-01-01 19:00:00') EndTime union all
    select convert(datetime, '2015-01-02 08:00:00') BeginTime, convert(datetime, '2015-01-02 20:00:00') EndTime union all
    select convert(datetime, '2015-01-03 08:00:00') BeginTime, convert(datetime, '2015-01-06 12:00:00') EndTime
)
select BTime
, sum(case 
when b.BeginTime between a.BTime and a.ETime and b.EndTime between a.BTime and a.ETime then convert(float, b.EndTime-b.BeginTime) * 24 
when b.BeginTime between a.BTime and a.ETime then convert(float, a.ETime-b.BeginTime) * 24 
when b.EndTime between a.BTime and a.ETime then convert(float, b.EndTime-a.BTime) * 24 
else 24
End) SumTime
from cte1 a, cte2 b 
where a.ETime>b.BeginTime and a.BTime<b.EndTime
group by BTime

#5

引用 4 楼 zbdzjx 的回复:

with
cte1 as  --存放每天的开始和结束时间
(
    select convert(datetime, '2015-01-01 00:00:00') BTime, convert(datetime, '2015-01-02 00:00:00') ETime union all
    select convert(datetime, '2015-01-02 00:00:00') BTime, convert(datetime, '2015-01-03 00:00:00') ETime union all
    select convert(datetime, '2015-01-03 00:00:00') BTime, convert(datetime, '2015-01-04 00:00:00') ETime union all
    select convert(datetime, '2015-01-04 00:00:00') BTime, convert(datetime, '2015-01-05 00:00:00') ETime union all
    select convert(datetime, '2015-01-05 00:00:00') BTime, convert(datetime, '2015-01-06 00:00:00') ETime union all
    select convert(datetime, '2015-01-06 00:00:00') BTime, convert(datetime, '2015-01-07 00:00:00') ETime union all
    select convert(datetime, '2015-01-07 00:00:00') BTime, convert(datetime, '2015-01-08 00:00:00') ETime union all
    select convert(datetime, '2015-01-08 00:00:00') BTime, convert(datetime, '2015-01-09 00:00:00') ETime 
),
cte2 as    --存放设备开机和关机时间
(
    select convert(datetime, '2015-01-01 08:00:00') BeginTime, convert(datetime, '2015-01-01 12:00:00') EndTime union all
    select convert(datetime, '2015-01-01 14:00:00') BeginTime, convert(datetime, '2015-01-01 19:00:00') EndTime union all
    select convert(datetime, '2015-01-02 08:00:00') BeginTime, convert(datetime, '2015-01-02 20:00:00') EndTime union all
    select convert(datetime, '2015-01-03 08:00:00') BeginTime, convert(datetime, '2015-01-06 12:00:00') EndTime
)
select BTime
, sum(case 
when b.BeginTime between a.BTime and a.ETime and b.EndTime between a.BTime and a.ETime then convert(float, b.EndTime-b.BeginTime) * 24 
when b.BeginTime between a.BTime and a.ETime then convert(float, a.ETime-b.BeginTime) * 24 
when b.EndTime between a.BTime and a.ETime then convert(float, b.EndTime-a.BTime) * 24 
else 24
End) SumTime
from cte1 a, cte2 b 
where a.ETime>b.BeginTime and a.BTime<b.EndTime
group by BTime

我读懂您的思路了，太感谢了，
4种情况就是我想要的，
太感谢了

大侠，您的sql写的这么溜，有没有什么好的书籍可以推荐啊？
小弟刚刚毕业当程序员，工作效率总是很低。。。