Generate a random NUMBER of timestamps for each date INTERVAL

Question

Below I am generating rows every 15 minutes from a start time to an end time.

Within the first start and END time 25-JAN-2023 09:00:00.000000 25-JAN-2023 09:15:00.000000

I want to pass these values to the function random_timestamp and generate a random number of timestamps (ie 3-10), which fall between the two timestamps.

I get the next group of times 25-JAN-2023 09:15:00.000000 25-JAN-2023 09:30:00.000000

Call the random_timestamp function again to generate a random number (ie 3-10) of timestamps for that period

I repeat the process until I hit the last set of times

25-JAN-2023 11:45:00.000000 25-JAN-2023 12:00:00.000000

I saw something about a lag function and I think that may help but I'm unsure how to implement it into my example where I need to pass two values to my function.

I know I need to put this logic in my code

SELECT random_timestamp( 
TIMESTAMP '2023-01-25 11:45:00',
 TIMESTAMP '2023-01-25 12:00:00') from dual connect by level <= ( select dbms_random.value ( 3, 10 ) n from dual )

in place of 

select dt from dt;

Below is my test CASE and an example of the desired output. Any help would be appreciated.

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';

with dt (dt, interv) as (
select TIMESTAMP '2023-01-25 09:00:00',
 numtodsinterval(15,'MINUTE') from dual
union all
select dt.dt + interv, interv from dt
where dt.dt + interv <= TIMESTAMP '2023-01-25 12:00:00')
select dt from dt;
/

DT
25-JAN-2023  09:00:00.000000
25-JAN-2023  09:15:00.000000
25-JAN-2023  09:30:00.000000
25-JAN-2023  09:45:00.000000
25-JAN-2023  10:00:00.000000
25-JAN-2023  10:15:00.000000
25-JAN-2023  10:30:00.000000
25-JAN-2023  10:45:00.000000
25-JAN-2023  11:00:00.000000
25-JAN-2023  11:15:00.000000
25-JAN-2023  11:30:00.000000
25-JAN-2023  11:45:00.000000
25-JAN-2023  12:00:00.000000


CREATE OR REPLACE FUNCTION random_timestamp(
  p_from      IN TIMESTAMP,
  p_to        IN TIMESTAMP,
  p_fraction  IN VARCHAR2   DEFAULT 'Y'
) RETURN TIMESTAMP
IS
  return_val_y TIMESTAMP(9) := p_from + dbms_random.value() * (p_to - p_from);
  return_val_n TIMESTAMP(0) := return_val_y;
BEGIN
  RETURN CASE
         WHEN p_fraction LIKE 'Y%' OR p_fraction LIKE 'y%'
         THEN return_val_y
         ELSE return_val_n
         END;
END random_timestamp;
/

The end result should be something like this below. Note I put an empty line between each call to the function to show a different amount of rows for each call to my function.

25-JAN-2023  09:04:42.917984
25-JAN-2023  09:04:38.082448
25-JAN-2023  09:11:43.368529
25-JAN-2023  09:04:56.513339
25-JAN-2023  09:10:21.592329
25-JAN-2023  09:06:56.241198
25-JAN-2023  09:03:02.853214

25-JAN-2023  09:18:43.151379
25-JAN-2023  09:16:10.342814
25-JAN-2023  09:21:38.186374

…
…
…

25-JAN-2023  11:52:25.095462
25-JAN-2023  11:50:43.687866
25-JAN-2023  11:58:15.107269
25-JAN-2023  11:57:21.549818
25-JAN-2023  11:50:10.750542

Answer 1

You can use:

WITH dt (dt, interv, rnd) AS (
  SELECT TIMESTAMP '2023-01-25 09:00:00',
         INTERVAL '15' MINUTE,
         FLOOR(DBMS_RANDOM.VALUE(3,11))
  FROM   DUAL
UNION ALL
  SELECT dt.dt + interv,
         interv,
         FLOOR(DBMS_RANDOM.VALUE(3,11))
  FROM   dt
  WHERE  dt.dt + interv <= TIMESTAMP '2023-01-25 12:00:00'
)
SELECT random_timestamp(dt, dt + INTERVAL '15' MINUTE) AS dt
FROM   dt
       CROSS APPLY (
           SELECT LEVEL AS lvl
           FROM   DUAL
           CONNECT BY LEVEL <= rnd
       )
ORDER BY dt;

fiddle