title: Oracle random time and interval
body: <p>I'm looking to generate times to create a schedule, which has multiple locations that need to be checked.</p>
  <p>Picture a prison guard who has to walk around a jail and check every cell. The schedule will start at some random time MMDDYYYY HH24:MI and then has between 6 - 10 minutes to check the next cell, after that 6 - 10 minutes to check the cell after that and so on and so until the schedule is completed.</p>
  <p>Let me illustrate with a simple example.</p>
  <p>Schedule_id location_id
  Schedule_time</p>
  <p>1 100 07132020 16:10:00</p>
  <p>-- start of schedule</p>
  <p>1 103 07132020 16:07:00</p>
  <p>-- guard has to be at location 103 by 16:07</p>
  <p>1 110 07132020 16:16:00</p>
  <p>-- guard has to be at location 110 by 16:16</p>
  <p>Since I randomly generated 3 rows for schedule 1 this is completed then I create the next  schedule.</p>
  <p>-- start of schedule but say I randomly GENERATED 5 rows for this schedule.</p>
  <p>2 102 07132020 23:46:00</p>
  <p>-- guard has to be at location 102 by 23:46</p>
  <p>2 104 07132020 23:56:00</p>
  <p>-- guard has to be at location 104 by 23:56</p>
  <p>-- WAIT!! I've generated 5 rows for schedule 2 but the next interval will cross midnight so I stop with 2 rows then go on to create the next schedule at some random HH24:MI but the MMDDYYYY will still be the same 07132020.</p>
  <p>THE STOP GAP is either we populated the rows with times OR it can't cross midnight!!</p>
  <p>-- start of new schedule. Note same MMDDYYYY</p>
  <p>3 223 07132020 02:04:00</p>
  <p>3 143 07132020 02:11:00</p>
  <p>3 46 07132020 02:17:00</p>
  <p>Your first example was almost there except there was NO STOP GAP from preventing it from crossing midnight and generating the next schedule.</p>
  <p>I have a location table too where I want to randomly choose a location_id but I can't use dbms_random since the values aren't consecutively numbered. I will try and figure that out. I'm mentioning it to complete the scenario.</p>
  <pre><code> CREATE TABLE schedule_hdr AS
  SELECT level AS schedule_id,
     'Schedule ' || level AS schedule_name
  
   FROM   dual
  CONNECT BY level &lt;= 10;
  
  
  
  CREATE TABLE  locations(
  location_id NUMBER(4), 
  location_name VARCHAR2(30)
  );
  
  INSERT INTO locations (
  location_id, 
  location_name 
  )
  VALUES 
  (46, 'Door 1');
  
  INSERT INTO locations (
  location_id, 
  location_name 
  )
  VALUES 
  (143, 'Door 2');
  
  INSERT INTO locations (
  location_id, 
  location_name 
  )
  VALUES 
  (223, 'Door 3');
  
  WITH random_times (     schedule_id,    schedule_name, datetime, lvl ) AS (
  SELECT schedule_id,
       schedule_name, 
       TRUNC(sysdate)
       + NUMTODSINTERVAL( FLOOR(DBMS_RANDOM.VALUE(0,23*60)), 'MINUTE' ),
       1
    FROM   schedule_hdr
   UNION ALL
   SELECT schedule_id,
       schedule_name, 
       datetime + NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(6,11)), 'MINUTE'),
       lvl + 1
    FROM   random_times
    WHERE  lvl &lt; 5
  )
  SELECT schedule_id,
     schedule_name,
     datetime
  FROM   random_times
     ORDER BY schedule_id, datetime;
  </code></pre>
  <p>My initial attempt  but the date is. always random and there is no interval</p>
  <pre><code>WITH rws  as (     select level rn from   dual connect by level &lt;= 5 ), 
     scheds as ( select sh.*, round (dbms_random.value(1,5) ) n from schedule_hdr sh )
    select   schedule_id,
    TRUNC(sysdate) + DBMS_RANDOM.value(0,86400)/86400
    from   rws 
    join   scheds s  on rn &lt;= n
    Order by schedule_id;
  </code></pre>