Assigning duration to each half hour interval between two dates

Question

enter image description here

I'm having difficulty with taking two dates and assigning duration in secs between all half hour intervals.

attached table data and attached expected output.

I tried hierarchy function and it failed for me. Any help appreciated.

Answer 1

You can generate a list of half-hour time periods, within whatever range you need, with a hierarchical query or a recursive CTE:

with p (start_time, stop_time) as (
  select cast(timestamp '2022-10-04 09:00:00' as date),
    cast(timestamp '2022-10-04 09:00:00' as date) + interval '30' minute
  from dual
  union all
  select p.stop_time, p.stop_time + interval '30' minute
  from p
  where p.stop_time < timestamp '2022-10-04 13:00:00'
)
select * from p

START_TIME	STOP_TIME
2022-10-04 09:00:00	2022-10-04 09:30:00
2022-10-04 09:30:00	2022-10-04 10:00:00
2022-10-04 10:00:00	2022-10-04 10:30:00
2022-10-04 10:30:00	2022-10-04 11:00:00
2022-10-04 11:00:00	2022-10-04 11:30:00
2022-10-04 11:30:00	2022-10-04 12:00:00
2022-10-04 12:00:00	2022-10-04 12:30:00
2022-10-04 12:30:00	2022-10-04 13:00:00

Then you can (outer) join that to your actual data looking for overlapping ranges, and calculate how much of the overlap falls in the time period - here I'm using greatest/least, and subtracting those. That gives the difference in days, which you can multiply by 24x24x60 to get the value in seconds. Then sum those up for each time period.

with p (start_time, stop_time) as (
  select cast(timestamp '2022-10-04 09:00:00' as date),
    cast(timestamp '2022-10-04 09:00:00' as date) + interval '30' minute
  from dual
  union all
  select p.stop_time, p.stop_time + interval '30' minute
  from p
  where p.stop_time < timestamp '2022-10-04 13:00:00'
)
select p.start_time,
  sum(round(
    (least(p.stop_time, t.stop_time) - greatest(p.start_time, t.start_time))
    * 86400
  )) as total_secs
from p
left join your_table t
on t.start_time <= p.stop_time and t.stop_time >= p.start_time
group by p.start_time
order by p.start_time

START_TIME	TOTAL_SECS
2022-10-04 09:00:00	1800
2022-10-04 09:30:00	891
2022-10-04 10:00:00	1800
2022-10-04 10:30:00	1800
2022-10-04 11:00:00	900
2022-10-04 11:30:00	1800
2022-10-04 12:00:00	1800
2022-10-04 12:30:00	1754

fiddle including the intermediate calculations.

I've got a difference number than you for two of the periods - 891 instead of 971, and 1754 instead of 1726. That seems to be what your data should give though:

• for the 09:30 period, the first row stops at 09:37:31, so the period includes 7:31 from that row; the second row starts at 09:52:40, so the period includes 7:20 from that; that totals 14:51 which is 891 seconds.
• for the 12:30 period, the third row ends at 12:59:14, to the period includes 29:14 from the third row, which is 1754 seconds.