Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Paul .

Asked: January 25, 2023 - 10:58 pm UTC

Last updated: January 31, 2023 - 7:01 am UTC

Version: 19c

Viewed 1000+ times

You Asked

I need to CREATE some test data where I want to CREATE N number of timestamps between 2 times for every 15 minute interval including the start time and less than the end time.

N would be defined as dbms_random between a low_number and a high_number.

I want to run something like this get_dates TIMESTAMP'2023-01-25 03:00:00', TIMESTAMP'2023-01-27 13:30:00', 10, 25

Any help would be greatly appreciated.



and Chris said...

A query like this can generate a random number of rows (between 3 and 10 in this case) at 15-minute intervals for each row:

with rws as (
  select level - 1 x from dual
  connect by level <= ( select dbms_random.value ( 3, 10 ) n from dual )
)
  select timestamp'2023-02-01 00:00:00' +
           numtodsinterval ( x * 15, 'minute' ) ts
  from   rws;
/*  
TS                            
------------------------------
01-FEB-2023 00.00.00.000000000
01-FEB-2023 00.15.00.000000000
01-FEB-2023 00.30.00.000000000
01-FEB-2023 00.45.00.000000000
01-FEB-2023 01.00.00.000000000
01-FEB-2023 01.15.00.000000000
*/


You put this in a SQL macro if you want this to be a reusable function.

If you want more details on (date/timestamp) row generation, including an overview of how to make a macro, see:

https://blogs.oracle.com/sql/post/how-to-generate-days-weeks-or-months-between-two-dates-in-oracle-database

Rating

  (4 ratings)

Comments

CREATE random number of timestamps between each INTERVAL

Paul, January 26, 2023 - 7:38 pm UTC

Thanks for your response but it didn't answer my question . I'm looking to CREATE N NUMBER of random timestamps within each row, which was generated something like wrapping this function in your code.

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     := p_from + dbms_random.value () * (p_to - p_from + INTERVAL '1' DAY);
      return_val_n TIMESTAMP (0) := return_val_y;
 BEGIN
      RETURN  CASE
                 WHEN UPPER (SUBSTR (p_fraction, 1, 1)) = 'Y'
    THEN return_val_y
    ELSE return_val_N
       END;
END random_timestamp;
/


Chris Saxon
January 27, 2023 - 4:58 pm UTC

I don't understand what you mean - what does random timestamps within each row look like?

Please clarify showing the output you expect with a full description of how the algorithm works.

CREATE random number of timestamps between each INTERVAL

Paul, January 27, 2023 - 7:47 pm UTC

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 (ie 3,10) of timestamps, 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


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

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


Chris Saxon
January 30, 2023 - 11:57 am UTC

Thanks for the update - see the example from mathguy below.

mathguy, January 29, 2023 - 7:40 pm UTC

I think this is what you are trying to do.

PROBLEM: given a global start timestamp GL_START_TS, a global end timestamp GL_END_TS and an interval length INT_LEN with GL_START_TS < GL_END_TS and INT_LEN > 0, generate random timestamps between GL_START_TS and GL_END_TS so that in each interval of length INT_LEN, starting from GL_START_TS, there is a random number of values, with the count in each interval between N1 and N2 (where N1 and N2 are positive integers, N1 <= N2, also given as inputs). It is not clear if you assume that the difference GL_END_TS - GL_START_TS is an integer multiple of INT_LEN, and if it isn't, what should happen in the last, incomplete interval (imagine 15 minute intervals starting at 10:10 and ending at 12:20, for example - what should happen between 12:10 and 12:20?) In the solution I propose below, I assume there will be 0 values in that last, incomplete interval. If the total interval is an integer multiple of INT_LEN then the question is moot.

The inputs are hardcoded below; you can provide bind variables instead, or any other mechanism - that isn't what your question is about.

with
  inputs (gl_start_ts, gl_end_ts, int_len, n1, n2) as (
    select  timestamp '2023-01-25 09:00:00', timestamp '2023-01-25 12:00:00', interval '15' minute, 3, 10
    from    dual
  )
, intervals (start_ts, int_len, n) as (
    select  gl_start_ts + (level - 1) * int_len, int_len, trunc(dbms_random.value(n1, n2 + 1))
    from    inputs
    connect by   level * int_len <= gl_end_ts - gl_start_ts
    start   with         int_len <= gl_end_ts - gl_start_ts
  )
select ts
from   intervals cross join lateral
       ( select  start_ts + dbms_random.value() * int_len as ts
         from    dual
         connect by level <= n
       )
;

Chris Saxon
January 30, 2023 - 11:58 am UTC

Great work mathguy - my understanding is the same as yours; hopefully Paul can confirm!

Thanks for providing a solution

Paul, January 30, 2023 - 12:10 pm UTC

Perfect exactly what I was looking for. Thank you both!! Issue closed
Connor McDonald
January 31, 2023 - 7:01 am UTC

glad we could help

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.