Oracle Generating Schedule Rows With An Interval

I have some SQL that generates rows for every 5 minutes. How can this be modified to get rid of overlapping times (see below) Note: Each row should be associated with a location_id

Solution 1:

You may avoid recursive query or loop, because you essentially need a row number of each row in locations table. So you'll need to provide an appropriate sort order to the analytic function. Below is the query:

with a as (
  selectdate'2021-01-01'+ to_dsinterval('0 23:30:00')
    as start_dt_param
  from dual
)
, date_gen as (
  select
    location_id
    , start_dt_param
    , start_dt_param + (row_number() over(orderby location_id) -1)
        *interval'10'minuteas start_dt
    , start_dt_param + (row_number() over(orderby location_id) -1)
        *interval'10'minute+interval'5'minuteas end_dt
from a
  crossjoin locations
)
select
  location_id
  , start_dt
  , end_dt
from date_gen
where end_dt < trunc(start_dt_param +1)

LOCATION_ID | START_DT            | END_DT             
----------: | :------------------ | :------------------
          1 | 2021-01-01 23:30:00 | 2021-01-01 23:35:00
          2 | 2021-01-01 23:40:00 | 2021-01-01 23:45:00
          3 | 2021-01-01 23:50:00 | 2021-01-01 23:55:00

UPD: Or if you wish a procedure, then it is even simpler. Because from 12c Oracle has fetch first addition, and analytic function may be simplified to rownum pseudocolumn:

createor replace procedure populate_schedule (
  p_schedule_id in number
  , p_start_date indate
) asbegininsertinto schedule (schedule_id, location_id, start_date, end_date)
  select
    p_schedule_id
    , location_id
    , p_start_date + (rownum -1) *interval'10'minute
    , p_start_date + (rownum -1) *interval'10'minute+interval'5'minutefrom locations
  /*Put your order of location assignment here*/orderby location_id
  /*The number of 10-minute intervals before midnight from the first end_date*/fetchfirst ((trunc(p_start_date +1) - p_start_date +1/24/60*5)*24*60/10) rowsonly
  ;
  
  commit;
end;
/

begin
  populate_schedule(1, timestamp'2020-01-01 23:37:00');
  populate_schedule(2, timestamp'2020-01-01 23:35:00');
  populate_schedule(3, timestamp'2020-01-01 23:33:00');
end;/

select *
from schedule
orderby schedule_id, start_date

SCHEDULE_ID | LOCATION_ID | START_DATE          | END_DATE           
----------: | ----------: | :------------------ | :------------------
          1 |           1 | 2020-01-01 23:37:00 | 2020-01-01 23:42:00
          1 |           2 | 2020-01-01 23:47:00 | 2020-01-01 23:52:00
          2 |           1 | 2020-01-01 23:35:00 | 2020-01-01 23:40:00
          2 |           2 | 2020-01-01 23:45:00 | 2020-01-01 23:50:00
          2 |           3 | 2020-01-01 23:55:00 | 2020-01-02 00:00:00
          3 |           1 | 2020-01-01 23:33:00 | 2020-01-01 23:38:00
          3 |           2 | 2020-01-01 23:43:00 | 2020-01-01 23:48:00
          3 |           3 | 2020-01-01 23:53:00 | 2020-01-01 23:58:00

db<>fiddle here

Solution 2:

Just loop every 10 minutes instead of every 5 minutes:

WITH input (start_time) AS (
  SELECT TRUNC(SYSDATE) +INTERVAL'23:30'HOURTOMINUTEFROM DUAL
)
SELECT start_time + (LEVEL-1) *INTERVAL'10'MINUTEAS t_from,
       start_time + (LEVEL-1) *INTERVAL'10'MINUTE+INTERVAL'5'MINUTEAS t_to
FROM   input
CONNECTBY (LEVEL-1) *INTERVAL'10'MINUTE<INTERVAL'1'DAYAND    LEVEL <= (SELECTCOUNT(*) FROM locations)
AND    start_time + (LEVEL-1) *INTERVAL'10'MINUTE< TRUNC(start_time) +INTERVAL'1'DAY;

db<>fiddle here

Solution 3:

A CTE is certainly the fastest solution. If you like to get more flexibility for intervals then you can use the SCHEDULER SCHEDULE. As drawback the performance might be weaker.

CREATEOR REPLACE TYPE TimestampRecType AS OBJECT (     
    T_FROM TIMESTAMP(0), 
    T_TO TIMESTAMP(0)
);
CREATEOR REPLACE TYPE TimestampTableType ISTABLEOF TimestampRecType;

CREATEOR REPLACE FUNCTION GetGchedule(
   start_time INTIMESTAMP, 
   stop_time inTIMESTAMPDEFAULT TRUNC(SYSDATE)+1) 
RETURN TimestampTableType AS
    
    ret TimestampTableType := TimestampTableType();
    return_date_after TIMESTAMP := start_time;
    next_run_date TIMESTAMP ;
    
BEGIN
    LOOP
        DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=MINUTELY;INTERVAL=5;', NULL, return_date_after, next_run_date);
        ret.EXTEND;
        ret(ret.LAST) := TimestampRecType(return_date_after, next_run_date);
        return_date_after := next_run_date;
        EXIT WHEN next_run_date >= stop_time;
    END LOOP;
    RETURN ret;
END;

SELECT*FROMTABLE(GetGchedule(trunc(sysdate)));

See syntax for calendar here: Calendaring Syntax