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.

CodePudding user response：

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

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

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.

CodePudding user response：

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:

fiddle