SQL server计算每天的总分钟数

Question

我试图计算每天干预的时间:想象一下下面的场景:

tbody> <<tr>

干预开始	干预结束	总时间
01/09/2021 10:00:00	01/09/2021 12:00:00	01/09/2021 02:00:00
02/09/2021 23:30:00	03/09/2021 01:30:00	02/09/2021 00:30:00和03/09/2021 01:30:00

您可以使用毫秒，秒，分钟…根据您的精度要求。即:

select interventionStart, interventionEnd, datediff(seconds, interventionStart, interventionEnd) totalTime
from myTable;

然后你可以将秒数转换为你想要的显示时间(例如:在。net中你可以使用TimeSpan的ToString()方法)

编辑:如果你绝对需要"每个日期"的次数，那么你可以这样做:

WITH
adjusted AS (
SELECT
interventionStart, interventionEnd
FROM  myTable
WHERE CAST(interventionStart AS DATE)=CAST(interventionEnd AS DATE)
UNION ALL
SELECT
interventionStart, CAST(interventionEnd AS DATE) interventionEnd
FROM  myTable
WHERE CAST(interventionStart AS DATE)!=CAST(interventionEnd AS DATE)
UNION ALL
SELECT
CAST(interventionEnd AS DATE) interventionStart, interventionEnd
FROM  myTable
WHERE CAST(interventionStart AS DATE)!=CAST(interventionEnd AS DATE)
)
SELECT
adjusted.interventionStart
, adjusted.interventionEnd
, DATEDIFF(SECOND, interventionStart, interventionEnd) totalTime
, DATEADD(
SECOND, DATEDIFF(SECOND, interventionStart, interventionEnd)
, CAST(CAST(adjusted.interventionStart AS DATE) AS DATETIME)
)                                             ifYouWish
FROM adjusted;

DbFiddle demo在这里

如果它回答了你的问题，你可以试试。

SELECT Intervention_Start, Intervention_End, AGE(Intervention_Start, 
Intervention_End) AS Total_Time from <table_name>; 

以秒为单位的时间。你可以随意转换成分钟，小时

select cast(c.d as date) dt, datediff(second, case when c.d > m.InterventionStart then c.d else m.InterventionStart end,
case when c2.d < m.InterventionEnd then c2.d else m.InterventionEnd end) seconds
from calendar c -- contains d = datetime of the start of the day, 2021-09-01 00:00:00 etc
-- next day
cross apply (values (dateadd(day, 1, c.d))) c2(d)
left join mytable m on c.d < m.InterventionEnd and m.InterventionStart < c2.d 

db-fiddle