使用T-SQL计算时间间隔

Question

给定两个日期/时间：

@start_date = '2009-04-15 10:24:00.000'
@end_date = '2009-04-16 19:43:01.000'

能否在以下格式中计算两个日期之间的经过时间

1天9小时19分钟

- jdiaz

Answer 1

你可以获取两个日期间的差异，以任意你想要的精度为单位（在你的例子中是分钟）：

您可以获取两个日期之间的差异，精确到任何你想要的分辨率（在你的例子中是分钟）：

DATEDIFF(minute, @start_date, @end_date)

从那里开始，只需将分钟转换成小时并将小时转换成天，并对余数进行模运算即可。

- Rex M

---

我可以理解DATEDIFF的工作原理，但它总是返回总时间。我可以获得总天数或总小时数，但我无法以直接的方式获得1天9小时的时间跨度。有例子吗？ - jdiaz

@jdiaz 如答案所述，您需要以所需的精度获取总数。如果您需要分钟精度，则获取分钟数，然后除以60以获取小时数（余数为分钟），然后将小时数除以24以获取天数（余数为小时）。 - Rex M

如果您使用DATEDIFF（second，@start_date，@end_date），则此示例http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-convert-a-timespan-in-seconds-to-hh-mm-ss.html将帮助您开始将秒转换为HH：MM：SS。 - JJS

@JJS 这个链接已经失效了。 - needfulthing

这应该可以解决问题：SELECT CAST(DATEDIFF(second, @start_date, @end_date) / 3600 AS varchar(max)) + ':'

• RIGHT('0' + CAST(DATEDIFF(second, @start_date, @end_date) % 3600 / 60 AS varchar(2)), 2) + ':'
• RIGHT('0' + CAST(DATEDIFF(second, @start_date, @end_date) % 60 AS varchar(2)), 2) - 哎呀，有人知道如何在注释中转义“@”符号吗？

- needfulthing

@needfulthing 使用archive.org - https://web.archive.org/web/20071211222018/http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-convert-a-timespan-in-seconds-to-hh-mm-ss.html。 - JJS

Answer 2

我知道这个帖子有些旧了，原来的参与者可能已经不再关注了，但我偶然发现它，并且最近已经编写了一些非常接近jdiaz所请求的代码。结果以D:H:M:S格式呈现为字符串。

第一步是获取时间跨度（以秒为单位）：

DECLARE @ElapsedS INT
SET @ElapsedS = DATEDIFF(second, @start_date, @end_date)

现在创建以下标量函数：

CREATE FUNCTION [dbo].[udfTimeSpanFromSeconds]
(
    @Seconds int
)
RETURNS varchar(15)
AS
BEGIN
DECLARE 
    --Variable to hold our result
      @DHMS varchar(15)
    --Integers for doing the math
    , @Days int --Integer days
    , @Hours int --Integer hours
    , @Minutes int --Integer minutes
    --Strings for providing the display
    , @sDays varchar(5) --String days
    , @sHours varchar(2) --String hours
    , @sMinutes varchar(2) --String minutes
    , @sSeconds varchar(2) --String seconds

--Get the values using modulos where appropriate
SET @Hours = @Seconds/3600
SET @Minutes = (@Seconds % 3600) /60
SET @Seconds = (@Seconds % 3600) % 60

--If we have 24 or more hours, split the @Hours value into days and hours
IF @Hours > 23 
BEGIN
    SET @Days = @Hours/24
    SET @Hours = (@Hours % 24)
END
ELSE
BEGIN
    SET @Days = 0
END

--Now render the whole thing as string values for display
SET @sDays = convert(varchar, @Days)
SET @sHours = RIGHT('0' + convert(varchar, @Hours), 2)
SET @sMinutes = RIGHT('0' + convert(varchar, @Minutes), 2)
SET @sSeconds = RIGHT('0' + convert(varchar, @Seconds), 2)

--Concatenate, concatenate, concatenate
SET @DHMS =  @sDays + ':' + @sHours + ':' + @sMinutes + ':' + @sSeconds

RETURN @DHMS

END

现在将时间间隔(feed your timespan)输入到新创建的函数中：

SELECT TimeSpan = dbo.udfTimeSpanFromSeconds(@ElapsedS)

应该输出 '1:09:19:01'

- stinkyP

Answer 3

CONVERT(
  varchar(8),
  (
    CAST(@end_date AS DATETIME)
    -
    CAST(@start_date AS DATETIME)
  )
  ,108
)

这将以HH:MM:SS的格式给您。

干杯！

- Dom

---

顺便提一下，"108"是指如此描述的一种转换样式：https://msdn.microsoft.com/en-us/library/ms187928.aspx108 - 小时:分:秒您也可以尝试使用"114"来获取毫秒 114 - 小时:分:秒:毫秒(24小时制) - PeterVermont

注意：如果值是DATETIME2而不是DATETIME，则需要进行转换以进行减法运算。请参见https://dev59.com/SaDha4cB1Zd3GeqP_Crx#63753337。 - Anssssss

这应该是答案..它很简单并且有效。我将编辑以添加日期时间内容，并添加varchar大小。即使它有效，这是一个不良习惯。 - Nick.McDermaid

小心！这个结果会丢弃大于23小时59分钟59.999秒的任何内容。使用这个结果和原始输入值会得到9:19:01，并且会忽略掉天数。 - undefined

Answer 4

DATEDIFF 可能会返回令人费解的值。例如，下面两个日期相差一秒钟，但是如果使用以下参数调用 DATEDIFF 并按照其他人已经解释的方式进行解释，则返回 1 年：

SELECT DATEDIFF(year, '2005-12-31 23:59:59', '2006-01-01 00:00:00')

请查看 MSDN 文档以了解 DATEDIFF 的工作原理。

- Paul S...

---

如果你将精度定义为“年”，那么这里就没有什么难理解的地方了。如果你告诉数据库你想要秒级精度，你应该会得到你的1秒钟。 - Val

“直观”是大多数合理的人所期望的。在我看来，他们（可能没有阅读文档中的细则）会期望上述结果为0而不是1。如果你活了从1纳秒到364天23小时59分钟59秒的任何时间，你有多少岁？是0岁而不是1岁。即使你允许四舍五入到最近的一年，任何小于7个月的时间仍应该四舍五入为0岁。如果你说的是“年龄”（即出于法律目的）与“岁数”，那么你肯定不会四舍五入（你会截断即向下取整）。 - Tom

Answer 5

以下是如何在查询中格式化datediff（50d 8h 35m）的方法：

Declare @Date1 as Datetime, @Date2 as Datetime
Set @Date1 = '2005-01-01 08:00:00'
Set @Date2 = '2005-02-20 16:35:30'

Select
CAST(DATEDIFF(Minute,@Date1, @Date2)/60/24 as Varchar(50)) ++ 'd ' ++ 
CAST((DATEDIFF(Minute,@Date1, @Date2)/60)-((DATEDIFF(Minute,@Date1, @Date2)/60/24)*24) as Varchar(50)) ++ 'h ' ++
CAST((DATEDIFF(Minute,@Date1, @Date2)) - (DATEDIFF(HOUR,@Date1, @Date2)*60) as Varchar(50)) ++ 'm' as FormattedDateDiff

- Trevor Noel

Answer 6

datediff(datepart, date1, date2);

Rex的回答更加完整。

- tpdi

Answer 7

DECLARE @FirstDate DATETIME, @SecondDate DATETIME, @result VARCHAR(MAX)
SELECT @FirstDate = '2017-03-01 09:54:00.637', @SecondDate = GETDATE()

DECLARE @Day INT,@Month INT,@Hour INT, @Minute INT,@TotalSeconds INT,@Year INT
SELECT @TotalSeconds = ABS(DATEDIFF(SECOND,@FirstDate,@SecondDate))

-- Standard values in seconds
DECLARE @YearSeconds INT, @MonthSeconds INT, @DaySeconds INT, @HourSeconds INT, @MinuteSeconds INT

SELECT  @MinuteSeconds = 60
SELECT  @HourSeconds = 60 * @MinuteSeconds 
SELECT  @DaySeconds = 24 * @HourSeconds
SELECT  @MonthSeconds = 30 * @DaySeconds
SELECT  @YearSeconds = 12 * @MonthSeconds

--SELECT @MinuteSeconds AS [Minutes], @HourSeconds AS [Hours], @DaySeconds AS [Day],@MonthSeconds AS [Month],@YearSeconds AS [Year]

IF @TotalSeconds < @MinuteSeconds
BEGIN
    SELECT @result = CAST(@TotalSeconds AS NVARCHAR(20)) + ' seconds ago' 
END
ELSE IF @TotalSeconds < @HourSeconds
BEGIN
    SELECT @result = CAST(ABS(DATEDIFF(MINUTE,@FirstDate,@SecondDate)) AS NVARCHAR(20)) + ' minutes ago' 
END
ELSE IF @TotalSeconds < @DaySeconds
BEGIN
    SELECT @result = CAST(ABS(DATEDIFF(HOUR,@FirstDate,@SecondDate)) AS     NVARCHAR(20)) + ' hours ago' 
END
ELSE IF @TotalSeconds < @MonthSeconds
BEGIN
    SELECT @result = CAST(ABS(DATEDIFF(DAY,@FirstDate,@SecondDate)) AS NVARCHAR(20)) + ' days ago' 
END
ELSE IF @TotalSeconds < @YearSeconds
BEGIN
    SELECT @result = CAST(ABS(DATEDIFF(MONTH,@FirstDate,@SecondDate)) AS NVARCHAR(20)) + ' months ago' 
END
ELSE IF @TotalSeconds > @YearSeconds
BEGIN
    SELECT @result = CAST(ABS(DATEDIFF(YEAR,@FirstDate,@SecondDate)) AS NVARCHAR(20)) + ' year ago' 
END


SELECT @result

- Jignesh

---

结果示例，“2个月前”等。 - Jignesh