Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

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

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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;

Here is a db<>fiddle.

EDIT:

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...