sql - 两个日期之间的 DATEDIFF 返回时间戳

问题描述

解决了

CONCAT((DATEDIFF(Minute,START_DTTM,END_DTTM)/60),'h:',
       (DATEDIFF(Minute,START_DTTM,END_DTTM)%60) 'm') AS TotalTimeMissing

产生 TotalTimeMissing: 5h:13m

________

我正在尝试返回两个特定日期之间的时间戳值，以计算出包裹丢失和被发现之间的时间。

编辑：代码已更新以包含来自 Sami 的代码。我还添加了从原始代码中排除的附加代码。

这是当前代码：

USE PACKAGE

GO  

SELECT

        dp.LEGACY_ID

       ,dp.SURNAME

       ,dp.FORENAME

       ,dp.ETHNICITY_DESCRIPTION

       ,dp.BIRTH_DTTM

       ,DATEDIFF(YY, dp.BIRTH_DTTM, GETDATE()) -

            CASE

                  WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >=

                        RIGHT(CONVERT(VARCHAR(6), dp.BIRTH_DTTM, 12), 4)

                        THEN 0

                  ELSE 1

            END AS [Current Age]

--^Precise age calc due to potential LL inaccuracy

       ,mp.DIM_PERSON_ID

       ,mp.MISSING_STATUS

       ,mp.START_DTTM

       ,mp.END_DTTM

       ,dp.LEGACY_ID

       ,mp.RETURN_INT_OFFERED

       ,mp.RETURN_INT_ACCEPTED

       ,mp.RETURN_INT_DATE


FROM C_S.FACT_MISSING_PACKAGE AS mp

JOIN C_S.FACT_MISSING_PACKAGE AS dp ON mp.DIM_PERSON_ID = dp.DIM_PERSON_ID

WHERE CAST (mp.START_DTTM AS DATE)

              BETWEEN DATEADD(YY, -1, CAST (GETDATE() AS DATE)) AND CAST (GETDATE() AS DATE)

--^Displays all records within exactly 1 year of run date

       UNION (SELECT CONCAT(Value / 3600 / 24,

              ' Days ',

              RIGHT(CONCAT('00', Value / 3600 % 24), 2),

              ':',

              RIGHT(CONCAT('00', Value / 60 % 60), 2),

              ':',

              RIGHT(CONCAT('00',Value % 3600 % 60), 2)

       ) AS TotalTimeMissing

FROM

(

  SELECT mp.DIM_PERSON_ID, DATEDIFF(Second, mp.START_DTTM, mp.END_DTTM) Value

  FROM C_S.FACT_MISSING_PACKAGE AS mp

) T(Value))


ORDER BY START_DTTM ASC;

Sami 让我大部分时间到达那里，但是当我运行上面的代码时，我得到一个与 UNION 和 T 相关的错误，T 没有说明所需的列数。为了解决这个问题，我尝试将第一轮 SELECT 列放入 (SELECT CONCAT() 语句，但它会产生错误，所以当谈到如何修复它时，我有点不知所措？

我需要以 TotalTimeMissing 的形式返回所有这些列，并在最后添加一列

谢谢

标签： sqlsql-servertsqldatediff

解决方案

您可以先使用该DATEDIFF函数计算分钟差，然后计算小时和天数，因为 1 小时是 60 分钟，1 天是 1440 分钟。

请注意DATEDIFF SQL Server 中的工作原理：

此函数返回指定开始日期和结束日期之间跨越的指定日期部分边界的计数（作为有符号整数值）。

所以，

DATEDIFF(day, '2020-01-13 23:59:58', '2020-01-14 00:00:08')

将返回 1，即使差异只有几秒钟，因为给定的时间间隔跨越了一天（午夜）的边界。

这就是为什么您不应该DATEDIFF(day, ...)在这里使用，而是使用DATEDIFF(minute, ...)orDATEDIFF(second, ...)并根据经过的分钟数或秒数计算小时数和天数。

我将使用CROSS APPLY来避免多次键入长表达式。我在这里也使用整数除法/来丢弃小数部分，例如200 / 60 = 3.

Total days = total minutes / 1440 (discard fractional part)
Total hours = total minutes / 60 (discard fractional part)

但是，我们不需要总小时数，我们需要在总天数之后剩下的额外小时数，所以我们需要取 mod 24。

Hours = Total hours % 24

对于最后几分钟，我们只需要总天数和小时数之后的剩余分钟数，因此

Minutes = total minutes mod 60.

查询：

SELECT 
     dp.LEGACY_ID
    ,dp.SURNAME
    ,dp.FORENAME
    ,mp.DIM_PERSON_ID
    ,mp.MISSING_STATUS
    ,mp.START_DTTM
    ,mp.END_DTTM
    ,dp.LEGACY_ID
    ,STR(DiffMinutes / 1440) + ':' +        -- total days
     STR(DiffMinutes / 60 % 24) + ':' +     -- hours (0 .. 23)
     STR(DiffMinutes % 60) AS TimeMissing   -- minutes (0 .. 59)
FROM 
    MissingPackages AS mp
    JOIN DIM_PERSON AS dp ON mp.DIM_PERSON_ID = dp.DIM_PERSON_ID
    CROSS APPLY
    (
        SELECT DATEDIFF(minute, mp.START_DTTM, mp.END_DTTM) AS DiffMinutes
    ) AS A
ORDER BY START_DTTM ASC;

sql - 两个日期之间的 DATEDIFF 返回时间戳

问题描述

解决方案

推荐阅读