在SQL中计算日期差，以天:小时:分钟:秒的格式显示

Question

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

结果应为87:05:29:00。

您能否 kindly 提供此脚本？ 谢谢， 阿朱恩

- user1423779

---

你在用什么？sql-server，oracle，mysql。 - Arion

哪个关系型数据库管理系统和你尝试了什么？ - Rahul

他因此获得了26k的浏览量和一个金徽章 :| - OGHaza

Answer 1

如果你正在使用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)

请参考这里

- Arion

Answer 2

嗨，我曾经遇到过类似的问题，花了一些时间思考并找到了解决方案。我有一个包含用户订阅信息的表格，其中包括开始时间和结束时间。我的问题有点复杂，但基本上归结为以下几点：

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`

所以基本上这个查询的作用是通过减去第一行中的日期来计算天数。然后将完整天数的数量加到起始时间上，并将总和减去结束时间，这样剩下的就是当天的时间。之后你只需要从中选择小时、分钟和秒钟即可。如果你想要把它们全部放在一起，可以连接字符串，但这种方式可以让你进一步使用，而不像字符串那样受限。

- Ivan Kvolik

Answer 3

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

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

- Cristiano M. Dias