Code for response to https://stackoverflow.com/questions/57294336/snowflake-server-split-duration-by-hour

snowflake_split_date_range_into_intervals.sql

use warehouse WH_TEST;
use database DB_TEST;
use schema public;

/*

create or replace table calendar(calendar_date DATE)

insert into calendar(calendar_date)
select
dateadd(d,rid2,to_timestamp_ntz('1970-01-01')) as calendar_date
from
(
select 0 as rid2 union
select row_number() over (order by null) as rid2
from table (generator(rowcount => 100000))
) t
where dateadd(d,rid2,to_timestamp_ntz('1970-01-01')) < '2030-01-01'
order by 1 asc;

create or replace table interval(id integer, interval_start time);

insert into interval (id,interval_start)
select
id,
to_time(dateadd(hour,id,to_timestamp_ntz('1970-01-01')))
from
(
select 0 as id union
select row_number() over (order by null) as id
from table (generator(rowcount => 23))
) t;



create or replace table example1(id varchar(10), DURATION_START datetime, DURATION_END datetime);
-- drop table example1
truncate table example1;
--
insert into  example1 values('0abc23','2019-06-29 00:08:00.000','2019-06-29 09:18:00.000');
insert into  example1 values('0abc24','2019-06-28 11:07:45.000','2019-06-28  12:08:45.000');
insert into  example1 values('0abc25','2019-06-28 01:00:00.000','2019-06-29 02:15:00.000');
insert into  example1 values('0abc26','2019-06-28 00:08:00.000','2019-06-29 15:18:00.000');

select * from calendar;
select * from interval;
select * from example1;


select to_date(min(DURATION_START)) as startDate , to_date(max(DURATION_END)) as endDate, datediff(dd , to_date(min(DURATION_START)) ,to_date(max(DURATION_END)))  as days
from example1;

*/






select 
      f.id
     ,f.DURATION_START
     ,f.DURATION_END
     ,f.start_time_HOUR_START
     ,f.end_time_HOUR_START
     ,q.CALENDAR_DATE
     ,q.HOUR_START
     ,q.HOUR_END
     ,case
    -- when starts during interval and ends after interval
           when f.DURATION_START >= q.HOUR_START and f.DURATION_END >= dateadd(hour, 1, q.HOUR_START)
               then datediff(s, f.DURATION_START, dateadd(hour, 1, q.HOUR_START))
    -- when starts during interval and ends during interval
           when f.DURATION_START >= q.HOUR_START and f.DURATION_END <= dateadd(hour, 1, q.HOUR_START)
               then datediff(s, f.DURATION_START, f.DURATION_END)
    -- when starts before interval and ends during interval
           when f.DURATION_START <= q.HOUR_START and f.DURATION_END <= dateadd(hour, 1, q.HOUR_START)
               then datediff(s, q.HOUR_START, f.DURATION_END)
    -- entire interval , starts before, and ends after
           when
               f.DURATION_START <= q.HOUR_START and f.DURATION_END >= dateadd(hour, 1, q.HOUR_START)
               then datediff(s, q.HOUR_START, dateadd(hour, 1, q.HOUR_START))
           else 0 end as seconds_elapsed
from (
         select *
              , to_timestamp(
                 dateadd(s, datediff(s, '1970-01-01', DURATION_START) - (datediff(s, '1970-01-01', DURATION_START) % 3600),
                         '1970-01-01')) as start_time_HOUR_START
              , to_timestamp(
                 dateadd(s, datediff(s, '1970-01-01', DURATION_END) - (datediff(s, '1970-01-01', DURATION_END) % 3600),
                         '1970-01-01')) as end_time_HOUR_START
         from example1
     ) f
         inner join

     (
         select
                distinct
                q1.calendar_date
              -- , t2.rid2
              , dateadd(hour, t2.id, to_timestamp(q1.calendar_date)) as HOUR_START
              , dateadd(hour, t2.id + 1, to_timestamp(q1.calendar_date)) as HOUR_END
         from (
                  select calendar_date
                  from calendar
                  where calendar_date between (select to_date(min(DURATION_START)) from example1) and (select to_date(max(DURATION_END)) from example1)
              ) q1
                  cross join
              interval as t2
        -- order by HOUR_START
     ) q on q.HOUR_START between f.start_time_HOUR_START and f.end_time_HOUR_START

ORDER BY f.id
       , f.DURATION_START
       , f.DURATION_END
       , q.CALENDAR_DATE
       , q.HOUR_START
;



select f.id
     , f.DURATION_START
     , f.DURATION_END
     , SUM(case
    -- when starts during interval and ends after interval
               when f.DURATION_START >= q.HOUR_START and f.DURATION_END >= dateadd(hour, 1, q.HOUR_START)
                   then datediff(s, f.DURATION_START, dateadd(hour, 1, q.HOUR_START))
    -- when starts during interval and ends during interval
               when f.DURATION_START >= q.HOUR_START and f.DURATION_END <= dateadd(hour, 1, q.HOUR_START)
                   then datediff(s, f.DURATION_START, f.DURATION_END)
    -- if interval start is after end of interval... exclude
               when f.DURATION_START >= dateadd(hour, 1, q.HOUR_START) then 0
    -- when starts before interval and ends during interval
               when f.DURATION_START <= q.HOUR_START and f.DURATION_END <= dateadd(hour, 1, q.HOUR_START)
                   then datediff(s, q.HOUR_START, f.DURATION_END)
    -- entire interval , starts before, and ends after
               when
                       f.DURATION_START <= q.HOUR_START and f.DURATION_END >= dateadd(hour, 1, q.HOUR_START)
                   then datediff(s, q.HOUR_START, dateadd(hour, 1, q.HOUR_START))
               else 0 end) as seconds_elapsed
from (
         select *
              , to_timestamp(
                 dateadd(s, datediff(s, '1970-01-01', DURATION_START) - (datediff(s, '1970-01-01', DURATION_START) % 3600),
                         '1970-01-01')) as start_time_HOUR_START
              , to_timestamp(
                 dateadd(s, datediff(s, '1970-01-01', DURATION_END) - (datediff(s, '1970-01-01', DURATION_END) % 3600),
                         '1970-01-01')) as end_time_HOUR_START
         from example1
     ) f
         inner join

     (
         select
                distinct
                q1.calendar_date
              -- , t2.rid2
              , dateadd(hour, t2.id, to_timestamp(q1.calendar_date)) as HOUR_START
              , dateadd(hour, t2.id + 1, to_timestamp(q1.calendar_date)) as HOUR_END
         from (
                  select calendar_date
                  from calendar
                  where calendar_date between (select to_date(min(DURATION_START)) from example1) and (select to_date(max(DURATION_END)) from example1)
              ) q1
                  cross join
              interval as t2
        -- order by HOUR_START
     ) q on q.HOUR_START between f.start_time_HOUR_START and f.end_time_HOUR_START
GROUP BY f.id
       , f.DURATION_START
       , f.DURATION_END
ORDER BY f.id
       , f.DURATION_START
       , f.DURATION_END

;



select datediff(s, DURATION_START, DURATION_END)               as duration_seconds,
       datediff(s, DURATION_START, DURATION_END) / 60.00       as duration_minutes,
       datediff(s, DURATION_START, DURATION_END) / 3600.00     as duration_hours,
       datediff(s, '1970-01-01', DURATION_START) -
       (datediff(s, '1970-01-01', DURATION_START) % 3600) as numeric_date,
       dateadd(s, datediff(s, '1970-01-01', DURATION_START) - (datediff(s, '1970-01-01', DURATION_START) % 3600),
               '1970-01-01')                         as start_time_interval,
       dateadd(s, datediff(s, '1970-01-01', DURATION_END) - (datediff(s, '1970-01-01', DURATION_END) % 3600),
               '1970-01-01')                         as end_time_interval,
       *
from example1
order by id, DURATION_START;