sql - SQL Query to calculate the hours between two dates grouped by days

Question

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

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 

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

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

标签： sqlsql-serversql-server-2016

Answer 1

您可以使用递归 CTE 将日期分解为范围：

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<>fiddle。

编辑：

在 SQL Server 中，这看起来像：

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;

这是这个 db<>fiddle。