在SQL Server中计算以分钟为单位的时间差 [英] Calculate time difference in minutes in SQL Server
问题描述
我需要两分钟的时间差。我有开始时间和结束时间,如下所示:
I need the time difference between two times in minutes. I am having the start time and end time as shown below:
start time | End Time
11:15:00 | 13:15:00
10:45:00 | 18:59:00
我需要第一行的输出为45,60,15,对应于分别为11:15至12:00,12:00和13:00,13:00和13:15之间的差异。
I need the output for first row as 45,60,15 which corresponds to the tine difference between 11:15 and 12:00, 12:00 and 13:00, 13:00 and 13:15 respectively.
任何人请帮忙。感谢提前。
Anybody please help out. Thanks in advance.
推荐答案
以下工作原理如下:
SELECT Diff = CASE DATEDIFF(HOUR, StartTime, EndTime)
WHEN 0 THEN CAST(DATEDIFF(MINUTE, StartTime, EndTime) AS VARCHAR(10))
ELSE CAST(60 - DATEPART(MINUTE, StartTime) AS VARCHAR(10)) +
REPLICATE(',60', DATEDIFF(HOUR, StartTime, EndTime) - 1) +
+ ',' + CAST(DATEPART(MINUTE, EndTime) AS VARCHAR(10))
END
FROM (VALUES
(CAST('11:15' AS TIME), CAST('13:15' AS TIME)),
(CAST('10:45' AS TIME), CAST('18:59' AS TIME)),
(CAST('10:45' AS TIME), CAST('11:59' AS TIME))
) t (StartTime, EndTime);
要获得24列,您可以使用24个案例语句,如:
To get 24 columns, you could use 24 case statements, something like:
SELECT [0] = CASE WHEN DATEDIFF(HOUR, StartTime, EndTime) = 0
THEN DATEDIFF(MINUTE, StartTime, EndTime)
ELSE 60 - DATEPART(MINUTE, StartTime)
END,
[1] = CASE WHEN DATEDIFF(HOUR, StartTime, EndTime) = 1
THEN DATEPART(MINUTE, EndTime)
WHEN DATEDIFF(HOUR, StartTime, EndTime) > 1 THEN 60
END,
[2] = CASE WHEN DATEDIFF(HOUR, StartTime, EndTime) = 2
THEN DATEPART(MINUTE, EndTime)
WHEN DATEDIFF(HOUR, StartTime, EndTime) > 2 THEN 60
END -- ETC
FROM (VALUES
(CAST('11:15' AS TIME), CAST('13:15' AS TIME)),
(CAST('10:45' AS TIME), CAST('18:59' AS TIME)),
(CAST('10:45' AS TIME), CAST('11:59' AS TIME))
) t (StartTime, EndTime);
以下内容也起作用,可能会比反复重复同样的case语句更短: / p>
The following also works, and may end up shorter than repeating the same case statement over and over:
WITH Numbers (Number) AS
( SELECT ROW_NUMBER() OVER(ORDER BY t1.N) - 1
FROM (VALUES (1), (1), (1), (1), (1), (1)) AS t1 (N)
CROSS JOIN (VALUES (1), (1), (1), (1)) AS t2 (N)
), YourData AS
( SELECT StartTime, EndTime
FROM (VALUES
(CAST('11:15' AS TIME), CAST('13:15' AS TIME)),
(CAST('09:45' AS TIME), CAST('18:59' AS TIME)),
(CAST('10:45' AS TIME), CAST('11:59' AS TIME))
) AS t (StartTime, EndTime)
), PivotData AS
( SELECT t.StartTime,
t.EndTime,
n.Number,
MinuteDiff = CASE WHEN n.Number = 0 AND DATEDIFF(HOUR, StartTime, EndTime) = 0 THEN DATEDIFF(MINUTE, StartTime, EndTime)
WHEN n.Number = 0 THEN 60 - DATEPART(MINUTE, StartTime)
WHEN DATEDIFF(HOUR, t.StartTime, t.EndTime) <= n.Number THEN DATEPART(MINUTE, EndTime)
ELSE 60
END
FROM YourData AS t
INNER JOIN Numbers AS n
ON n.Number <= DATEDIFF(HOUR, StartTime, EndTime)
)
SELECT *
FROM PivotData AS d
PIVOT
( MAX(MinuteDiff)
FOR Number IN
( [0], [1], [2], [3], [4], [5],
[6], [7], [8], [9], [10], [11],
[12], [13], [14], [15], [16], [17],
[18], [19], [20], [21], [22], [23]
)
) AS pvt;
它通过加入一个24个数字的表来起作用,所以case语句不需要重复,然后使用 PIVOT
It works by joining to a table of 24 numbers, so the case statement doesn't need to be repeated, then rolling these 24 numbers back up into columns using PIVOT
这篇关于在SQL Server中计算以分钟为单位的时间差的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!