I'm trying to create a function, which returns a random TIMESTAMP between a range of timestamps.

It doesn't appear to be working ALL the time as sometimes I get a value back before the starting range and sometimes I get a value back after the ending range.

Below is my test CASE and example of a TIMESTAMP out of range.In this example the TIMESTAMP is after the ending range of TIMESTAMP '2023-01-25 12:00:00'

Can someone please explain what the problem is and show me how to fix it as I can't seem to figure this out.

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

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;
/

SELECT random_timestamp( 
TIMESTAMP '2023-01-25 09:00:00', TIMESTAMP '2023-01-25 12:00:00') as ts from dual

TS
26-JAN-2023  03:59:06.013730

CodePudding user response：

You are adding 1 day:

p_from   dbms_random.value () * (p_to - p_from   INTERVAL '1' DAY);

It falls within the range of p_from to p_to plus 1 day and is doing exactly what you told it to do.

If you don't want the range to be 1 day extra then remove INTERVAL '1' DAY

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;
/

Then:

SELECT MIN(ts),
       MAX(ts)
FROM   (
  SELECT random_timestamp(
           TIMESTAMP '2023-01-25 09:00:00',
           TIMESTAMP '2023-01-25 12:00:00'
         ) AS ts
  FROM   DUAL
  CONNECT BY LEVEL <= 1e6
);

May randomly output:

And stays within the range.

fiddle