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.

Can you please explain what is "duration in secs between all half hour intervals"? — sankar, Nov 09 '22 at 15:44
Where did you get 971 and 1726 from? The 09:30- period gets 7:31 from the first row and 7:20 from the second, and none from the third, which is 14:51 which is 891 seconds; and the 12:30- period get 29:14 from the third row, which is 1754. Or are you doing a different calculation - if so please explain your logic in the question. — Alex Poole, Nov 09 '22 at 16:21
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Nov 09 '22 at 16:22
Does this answer your question? [Generating dates between two dates](https://stackoverflow.com/questions/16207543/generating-dates-between-two-dates) And specifically for N minutes intervals: [Generate rows with time intervals between 2 dates in Oracle](https://stackoverflow.com/questions/50981401/generate-rows-with-time-intervals-between-2-dates-in-oracle) — astentx, Nov 09 '22 at 17:49

Alex Poole · Accepted Answer · 2022-11-09T16:31:57.363

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.

Thanks ! I'm not to diverse into hierarchy or recursive but explanation and example is perfect ! Thanks a bunch ! — user20459917, Nov 09 '22 at 18:31

score 0 · Answer 2 · answered Nov 09 '22 at 16:44

You can calculate the minimum and maximum times for each id and then generate a calendar of half-hour intervals and join that back to your table:

WITH bounds (id, min_start, max_stop) AS (
  SELECT id,
         TRUNC(CAST(MIN(start_dt) AS TIMESTAMP), 'HH')
         + CASE
           WHEN EXTRACT(MINUTE FROM CAST(MIN(start_dt) AS TIMESTAMP)) >= 30
           THEN INTERVAL '30' MINUTE
           ELSE INTERVAL '0' MINUTE
           END,
         TRUNC(CAST(MAX(stop_dt) AS TIMESTAMP), 'HH')
         + CASE
           WHEN EXTRACT(MINUTE FROM CAST(MAX(stop_dt) AS TIMESTAMP)) >= 30
           THEN INTERVAL '60' MINUTE
           ELSE INTERVAL '30' MINUTE
           END
  FROM   table_name
  GROUP BY id
),
half_hours (id, start_dt) AS (
  SELECT b.id, t.start_dt
  FROM   bounds b
         CROSS JOIN LATERAL(
           SELECT min_start + (LEVEL - 1) * INTERVAL '30' MINUTE AS start_dt
           FROM   DUAL
           CONNECT BY min_start + LEVEL * INTERVAL '30' MINUTE <= max_stop
         ) t
)
SELECT h.id,
       h.start_dt,
       h.start_dt + INTERVAL '30' MINUTE AS stop_dt,
       ROUND(
         SUM(
           LEAST(h.start_dt + INTERVAL '30' MINUTE, t.stop_dt)
           - GREATEST(h.start_dt, t.start_dt)
         ) * 24 * 60 * 60
       ) AS seconds
FROM   half_hours h
       LEFT OUTER JOIN table_name t
       ON (    h.id = t.id
           AND t.start_dt < h.start_dt + INTERVAL '30' MINUTE
           AND h.start_dt < t.stop_dt)
GROUP BY
       h.id,
       h.start_dt

Which, for the sample data:

CREATE TABLE table_name (ID, start_dt, stop_dt) AS
SELECT 5, DATE '2022-04-10' + INTERVAL '09:00:00' HOUR TO SECOND, DATE '2022-04-10' + INTERVAL '09:37:31' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 5, DATE '2022-04-10' + INTERVAL '09:52:40' HOUR TO SECOND, DATE '2022-04-10' + INTERVAL '11:15:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 5, DATE '2022-04-10' + INTERVAL '11:30:00' HOUR TO SECOND, DATE '2022-04-10' + INTERVAL '12:59:14' HOUR TO SECOND FROM DUAL;;

Outputs:

ID	START_DT	STOP_DT	SECONDS
5	2022-04-10 09:00:00	2022-04-10 09:30:00	1800
5	2022-04-10 09:30:00	2022-04-10 10:00:00	891
5	2022-04-10 10:00:00	2022-04-10 10:30:00	1800
5	2022-04-10 10:30:00	2022-04-10 11:00:00	1800
5	2022-04-10 11:00:00	2022-04-10 11:30:00	900
5	2022-04-10 11:30:00	2022-04-10 12:00:00	1800
5	2022-04-10 12:00:00	2022-04-10 12:30:00	1800
5	2022-04-10 12:30:00	2022-04-10 13:00:00	1754

fiddle

Assigning duration to each half hour interval between two dates

2 Answers2