SQL查询计算按天分组的两个日期之间的小时数 [英] SQL Query to calculate the hours between two dates grouped by days

问题描述

我需要为以下场景编写 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.

这篇关于SQL查询计算按天分组的两个日期之间的小时数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！