Oracle function to return random date and timestamp between VALUES

user_16771377 0
Viewed 46

I'm having some difficulties creating a function that returns a random DATE( next is a function that returns a random timestamp) when passed in a from and to date range.

I am looking to have the value also include a random time associated with the DATE. For the timestamp function a random fractional value.

Below is what I have so far for the DATE function but I can't seem to get it to compile. Any help would be greatly appreciated. Thanks to all who answer.


CREATE OR REPLACE FUNCTION random_date(
  p_from IN DATE,
  p_to   IN DATE
)
  RETURN date DETERMINISTIC
IS
  v_start DATE := TRUNC(LEAST(p_from, p_to));
  v_end   DATE := TRUNC(GREATEST(p_from, p_to));
RETURN  p_from
       + DBMS_RANDOM.VALUE(0, p_to -  p_from + 1);
END random_date;
/
date function oracle
2 Answers

It:

  • is missing the BEGIN keyword;
  • does not want it to be DETERMINISTIC (because it will not be):
CREATE FUNCTION random_date(
  p_from IN DATE,
  p_to   IN DATE
) RETURN DATE
IS
  c_from CONSTANT DATE := LEAST(p_from, p_to);
  c_to   CONSTANT DATE := GREATEST(p_from, p_to);
BEGIN
  RETURN c_from + TRUNC(DBMS_RANDOM.VALUE() * ((c_to - c_from) * 86400 + 1))
                  / 86400;
END random_date;
/

And, for TIMESTAMPs:

CREATE FUNCTION random_timestamp(
  p_from IN TIMESTAMP,
  p_to   IN TIMESTAMP
) RETURN TIMESTAMP
IS
  c_from CONSTANT TIMESTAMP(9) := LEAST(p_from, p_to);
  c_to   CONSTANT TIMESTAMP(9) := GREATEST(p_from, p_to);
BEGIN
  RETURN c_from + DBMS_RANDOM.VALUE()
                  * (c_to + INTERVAL '0.000000001' SECOND - c_from);
END random_timestamp;
/

Note: DBMS_RANDOM.VALUE() will return a value greater than or equal to 0 and less than 1 so if you want to include the p_to bound in the range then you need to increase the range by the smallest possible increment; hence adding 1 second for dates and 1e-9 seconds for timestamps.

The RANDOM_TIMESTAMP function above works but is not truly random as, due to rounding issues, the instant at either end of the range will occur with half the probability of all the other instants between the two extremes. For most cases, this is not a particular issue but if it is (particularly for small ranges of a few microseconds) then you need to convert the duration of the interval to an integer before applying the randomness:

CREATE OR REPLACE FUNCTION random_timestamp(
  p_from IN TIMESTAMP,
  p_to   IN TIMESTAMP
) RETURN TIMESTAMP
IS
  c_from TIMESTAMP(9) := LEAST(p_from, p_to);
  c_to   TIMESTAMP(9) := GREATEST(p_from, p_to);
  c_diff INTERVAL DAY(9) TO SECOND(9) := c_to - c_from;
  c_sdiff NUMBER(38,0) := EXTRACT(DAY FROM c_diff) * 86400e6
                       + EXTRACT(HOUR FROM c_diff) * 3600e6
                       + EXTRACT(MINUTE FROM c_diff) * 60e6
                       + EXTRACT(SECOND FROM c_diff) *  1e6
                       + 1;
BEGIN
  RETURN c_from + NUMTODSINTERVAL(
                    TRUNC(DBMS_RANDOM.VALUE() * c_sdiff) / 86400e6,
                    'DAY'
                  );
END random_timestamp;
/

db<>fiddle here

user_15092640

I can't generate a date or timestamp for the last day of the range, regardless of where that falls in the month. For example, random_date (DATE '2022-04-27', DATE '2022-05-03') generates dates on the last day of the month (April 30) but not on May 3.

If p_from is midnight on April 1, 2022 and p_to is midnight on April 30, 2022, then i need to generate a random number between 0 and 30, but (p_to - p_from) = 29, not 30. (Think about it; if p_to was also midnight on April 1, 2022, then (p_to - p_from) would be 0, not 1.) If i want a random DATE that is less than (never equal to ) midnight on May 1, then pass May 1 as the second argument to random_date. To do this automatically, I made the following changes to the functions.


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

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';


 CREATE OR REPLACE FUNCTION random_date(
      p_from IN DATE,
      p_to   IN DATE
    ) RETURN DATE
   IS
   BEGIN
      RETURN p_from + DBMS_RANDOM.VALUE() * (p_to - p_from + 1);
   END random_date;
   /

 CREATE OR REPLACE FUNCTION random_timestamp(
      p_from IN TIMESTAMP,
      p_to   IN TIMESTAMP
    ) RETURN TIMESTAMP
   IS
 BEGIN
      RETURN p_from + DBMS_RANDOM.VALUE() * (p_to - p_from + interval '1' day);
   END random_timestamp;
   /


CREATE TABLE t1 (     
 seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
   dt   DATE,
   ts TIMESTAMP 
);

 INSERT INTO t1 (dt, ts )
        SELECT
            random_date(DATE '2022-04-01', DATE '2022-04-30'),
            random_timestamp(DATE '2022-04-01', DATE '2022-04-30')
        FROM
            dual CONNECT BY level <= 10000;


select trunc(dt), count(*)
from t1
group by trunc(dt)
Order by trunc(dt);

select trunc(ts), count(*)
from t1
group by trunc(ts)
Order by trunc(ts);
user_167713770
Related