时间戳

首页 > 解决方案 > 日期之间一天的小时数

sql - 日期之间一天的小时数

问题描述

我有一个包含以下列的表：gkey、ata、atd

ata 和 atd 都是日期时间。

我想计算出每天有东西在它离开之前到达的时间。

示例数据：

+---------+-------------------------+-------------------------+
| gkey    | ata                     | atd                     |
+---------+-------------------------+-------------------------+
| 1142227 | 14/06/2017 17:30:00.000 | 15/06/2017 05:30:00.000 |
+---------+-------------------------+-------------------------+
| 1322667 | 18/01/2018 16:17:00.000 | 18/01/2018 20:45:00.000 |
+---------+-------------------------+-------------------------+
| 1339468 | 26/02/2018 23:57:00.000 | 27/02/2018 11:35:00.000 |
+---------+-------------------------+-------------------------+

预期结果：

14/06/2017 - 6.5 hours
15/06/2017 - 5.5 hours
18/01/2018 - 4.5 hours
26/02/2018 - 0.05 hours
27/02/2018 - 11.58 hours

标签： sqlsql-server

解决方案

您可以使用递归 CTE：

with cte as (
      select gkey, ata, atd,
             ata as day_start,
             (case when cast(ata as date) = cast(atd as date)
                   then atd
                   else dateadd(day, 1, cast(ata as date))
              end) as day_end
      from t
      union all
      select gkey, day_end, atd,
             day_end as day_start,
             (case when cast(day_end as date) = cast(atd as date)
                   then day_end
                   else dateadd(day, 1, cast(day_end as date))
              end) as day_end
      from cte
      where day_start < cast(atd as date)
     )
select cte.*,
       datediff(minute, day_start, day_end) / 60.0 as num_hours
from cte;

这是一个 SQL 小提琴。

推荐阅读