Generate a random NUMBER of timestamps for each date INTERVAL-CodePudding

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

CodePudding user response：

Get the difference in seconds between the from/to by casting to a date (easier to work with than intervals), then add in any fractional piece. Generate a random number between 0 and that difference, then add it back as seconds to the from date.

CREATE OR REPLACE FUNCTION random_timestamp (
  p_from      IN TIMESTAMP,
  p_to        IN TIMESTAMP
) RETURN TIMESTAMP
AS
  var_diff number;
BEGIN
  var_diff := ((CAST(p_to AS date) - CAST(p_from AS date)) * 86400)   (EXTRACT(SECOND FROM p_to) - EXTRACT(SECOND FROM p_from));
  RETURN NUMTODSINTERVAL(dbms_random.value(0,var_diff),'SECOND')   p_from;
END;

CodePudding user response：

You can use DBMS_RANDOM function to generate a value between 90 and 300 seconds (that give you 3-10 rows per 15 minutes) to be added to your timestamp.

WITH dt (dt) AS (
    SELECT TIMESTAMP '2023-01-25 09:00:00' 
           NUMTODSINTERVAL(DBMS_RANDOM.VALUE(90, 300),'SECOND') FROM dual

    UNION ALL

    SELECT dt.dt   NUMTODSINTERVAL(DBMS_RANDOM.VALUE(90, 300),'SECOND') 
    FROM dt
    WHERE dt.dt   NUMTODSINTERVAL(15,'MINUTE') <= TIMESTAMP '2023-01-25 12:00:00'
)
SELECT dt FROM dt;

Check the demo here.