SQL查询计算按天分组的两个日期之间的小时数

问题描述:

我需要为以下场景编写 SQL 查询.

I need to write an SQL query for the following scenario.

我的开始日期为 2020-01-10 13:00:00.347,结束日期为 2020-01-12 02:00:00.347,所以我需要将数据分组为

I am having start date as 2020-01-10 13:00:00.347 and end date as 2020-01-12 02:00:00.347, so I need data grouped as

Day              Hours
----             -----
10-01-2020       11
11-01-2020       24
12-01-2020       2.30 

这意味着第一天是 11 小时,第二天是 24 小时,第三天是 2.3 小时.

which means 11 hours was for the first date and 24 hours in second day and 2.3 hours on 3rd day.

获取上述格式数据的最高效 SQL 查询是什么?提前致谢.

What will the most Efficient SQL query to fetch the data in the above-mentioned format? Thanks in advance.

您可以使用递归 CTE 将日期分成多个范围:

You can use a recursive CTE to break the dates into ranges:

with recursive cte as (
       select start_date as day_start,
              (case when date(start_date) = date(end_date) then end_date else date(start_date) + interval 1 day end) as day_end,
              end_date
       from (select cast('2020-01-10 13:00:00.347' as datetime) as start_date,
                    cast('2020-01-12 02:00:00.347' as datetime) as end_date
            ) t
       union all
       select day_end,
              (case when date(day_end) = date(end_date) then end_date else date(day_end) + interval 1 day end) as day_end,
              end_date
       from cte
       where day_end <> end_date
     )
select day_start, day_end,
       timestampdiff(second, day_start, day_end) / (60 * 60)
from cte;

这里是db<>小提琴.

Here is a db<>fiddle.

在 SQL Server 中,这看起来像:

In SQL Server, this looks like:

with cte as (
       select start_date as day_start,
              (case when cast(start_date as date) = cast(end_date as date) then end_date else dateadd(day, 1, cast(start_date as date)) end) as day_end,
              end_date
       from (select cast('2020-01-10 13:00:00.347' as datetime) as start_date,
                    cast('2020-01-12 02:00:00.347' as datetime) as end_date
            ) t
       union all
       select day_end,
              (case when cast(day_end as date) = cast(end_date as date) then end_date else dateadd(day, 1, day_end) end) as day_end,
              end_date
       from cte
       where day_end <> end_date
     )
select day_start, day_end,
       datediff(second, day_start, day_end) / (60.0 * 60)
from cte;

这里是这个数据库小提琴.

Here is this db<>fiddle.