以天为单位计算SQL中的DateDiff：小时：分钟：秒格式

I am currently working an SQL script to calculate the difference between two dates which would give me the result in DD:HH:MI:SEC format. Example: Date 1: 7/30/12 4:00 PM Date 2: 5/4/12 10:31 AM

我目前正在编写一个SQL脚本来计算两个日期之间的差异，它会给我DD：HH：MI：SEC格式的结果。示例：日期1：7/30/12 4:00 PM日期2：5/4/12 10:31 AM

And the result should be 87:05:29:00

结果应该是87：05：29：00

Can you kindly help with the script for this? Regards, Arjun

你能帮忙写一下这个剧本吗？此致，Arjun

3 个解决方案

#1

If you are using sql-server then you can do this:

如果您使用的是sql-server，那么您可以这样做：

declare @x int, 
        @dt1 smalldatetime = '1996-03-25 03:24:16', 
        @dt2 smalldatetime = getdate()

set @x = datediff (s, @dt1, @dt2)


SELECT convert(varchar, @x / (60 * 60 * 24)) + ':'
+ convert(varchar, dateadd(s, @x, convert(datetime2, '0001-01-01')), 108)

Reference here

参考这里

#2

Well, you if want to perform some calculation, you could do this as well:

好吧，如果你想进行一些计算，你也可以这样做：

DECLARE @SecsInADay INT = 60 * 60 * 24
DECLARE @DATE1 DATETIME = CONVERT(DATETIME,'30/07/2012 16:00:00')
DECLARE @DATE2 DATETIME = CONVERT(DATETIME,'04/05/2012 10:31:00')
DECLARE @Days INT = DATEDIFF(DAY, @DATE2, @DATE1)
DECLARE @DiffInSeconds INT = DATEDIFF(SECOND, @DATE2, @DATE1)
DECLARE @TotalDaysInSeconds INT = @Days * @SecsInADay
DECLARE @RemainingHours INT = @DiffInSeconds - @TotalDaysInSeconds
DECLARE @Hours INT = @RemainingHours / 3600
DECLARE @Seconds INT = @RemainingHours % 3600
DECLARE @Minutes INT = @Seconds / 60
DECLARE @RemainingSeconds INT = @Seconds % 60

SELECT
CASE WHEN @Days < 10 THEN '0' + CAST(@Days AS VARCHAR) ELSE CAST(@Days AS VARCHAR) END + ':' +
CASE WHEN @Hours < 10 THEN '0' + CAST(@Hours AS VARCHAR) ELSE CAST(@Hours AS VARCHAR) END + ':' +
CASE WHEN @Minutes < 10 THEN '0' + CAST(@Minutes AS VARCHAR) ELSE CAST(@Minutes AS VARCHAR) END + ':' +
CASE WHEN @RemainingSeconds < 10 THEN '0' + CAST(@RemainingSeconds AS VARCHAR) ELSE CAST(@RemainingSeconds AS VARCHAR) END

#3

Hi i had a similar problem, took some time to think and here's my solution, I've had the Table with user subscriptions, there sad starting time and closing time, my problem was a bit more complex bit basically it came down to this:

嗨，我有一个类似的问题，花了一些时间思考，这是我的解决方案，我有用户订阅的表，有悲伤的开始时间和关闭时间，我的问题有点复杂位基本上它归结为：

SELECT subscription_id, time_open, time_closed, TIMESTAMPDIFF(DAY,time_open,time_closed) AS Day,

HOUR(sec_to_time(TIMESTAMPDIFF(SECOND,ADDDATE(`time_open`, INTERVAL TIMESTAMPDIFF(DAY,time_open,time_closed) DAY),`time_closed`))) AS Hour,

MINUTE(sec_to_time(TIMESTAMPDIFF(SECOND,ADDDATE(`time_open`, INTERVAL TIMESTAMPDIFF(DAY,time_open,time_closed) DAY),`time_closed`))) AS Minute,

SECOND(sec_to_time(TIMESTAMPDIFF(SECOND,ADDDATE(`time_open`, INTERVAL TIMESTAMPDIFF(DAY,time_open,time_closed) DAY),`time_closed`))) AS Second

FROM `user_subscription`

So basically what this query does is that it calculates days by deducing dates in the first line. Then it adds the number of the full days to the starting time and reduces the closing time my the sum, so what remains is the time in the current day. After that you just select Hours, minutes, and seconds from that. If you want it all together you can concatenate the strings but this way you can use it further, unlike the strings.

所以这个查询基本上做的是它通过在第一行中推断日期来计算天数。然后它将整天的数量加到起始时间并减少我的总和的结束时间，所以剩下的是当天的时间。之后，您只需选择小时，分钟和秒。如果你想要它们，你可以连接字符串，但这样你可以进一步使用它，不像字符串。

#1