Generating random INTERVAL

Question

I have a function below that returns a random INTERVAL between a range of hours, which appears to be working fine but is currently limited to hours only.

I would would like to expand this functionality to also support returning a random INTERVAL for days, minutes by passing in a literal (ie 'DAY', 'MINUTE' or 'SECOND')

For example if I call random_interval (1,4, 'DAY') I would get something like this +000000002 11:24:43.000000000 or if i call random_interval (20,40, 'MINUTE') I would get something like +000000000 00:24:44.000000000

Thanks in advance to all who answer and for your time and expertise.


CREATE OR REPLACE FUNCTION random_interval(
      p_min_hours IN NUMBER,
      p_max_hours IN NUMBER
    ) RETURN INTERVAL DAY TO SECOND
   IS
   BEGIN
      RETURN floor(dbms_random.value(p_min_hours, p_max_hours)) * interval '1' hour
        + floor(dbms_random.value(0, 60)) * interval '1' minute
        + floor(dbms_random.value(0, 60)) * interval '1' second;
END random_interval;
/

SELECT random_interval(1, 10)  as random_val FROM dual CONNECT BY level <= 10 order by 1

RANDOM_VAL
+000000000 01:04:03.000000000
+000000000 03:14:52.000000000
+000000000 04:39:42.000000000
+000000000 05:00:39.000000000
+000000000 05:03:28.000000000
+000000000 07:03:19.000000000
+000000000 07:06:13.000000000
+000000000 08:50:55.000000000
+000000000 09:10:02.000000000
+000000000 09:26:44.000000000

score 2 · Answer 1 · answered Dec 04 '22 at 11:51

Try giving this a shot instead


CREATE OR REPLACE FUNCTION random_interval(
      p_min IN NUMBER,
      p_max IN NUMBER, 
      p_period VARCHAR2
    ) RETURN INTERVAL DAY TO SECOND
   IS
   BEGIN
      IF p_period = 'HOUR' THEN 
        RETURN floor(dbms_random.value(p_min, p_max)) * interval '1' hour
              + floor(dbms_random.value(0, 60)) * interval '1' minute
              + floor(dbms_random.value(0, 60)) * interval '1' second;
      ELSE IF p_period = 'DAY' THEN
        RETURN floor(dbms_random.value(p_min, p_max)) * interval '1' day
              + floor(dbms_random.value(0, 24)) * interval '1' hour
              + floor(dbms_random.value(0, 60)) * interval '1' minute
              + floor(dbms_random.value(0, 60)) * interval '1' second;  
      ELSE IF p_period = 'MINUTE' THEN
        RETURN floor(dbms_random.value(p_min, p_max)) * interval '1' minute
              + floor(dbms_random.value(0, 60)) * interval '1' second;  
      ELSE IF p_period = 'SECOND' THEN
        RETURN floor(dbms_random.value(p_min, p_max)) * interval '1' second;
      ELSE 
        RETURN NULL;
      END IF;
END random_interval;
/

SELECT random_interval(1, 10, 'DAY')  as random_val FROM dual CONNECT BY level <= 10 order by 1

RANDOM_VAL
+000000003 02:46:09.000000000
+000000004 19:19:56.000000000
+000000002 11:24:43.000000000
+000000002 16:20:44.000000000
+000000001 22:24:30.000000000
+000000002 15:14:38.000000000
+000000003 00:48:03.000000000
+000000003 18:08:13.000000000
+000000002 01:05:34.000000000
+000000002 08:12:19.000000000

I can't seem to get it to compile on Oracle live SQL. Errors: FUNCTION RANDOM_INTERVAL Line/Col: 25/5 PLS-00103: Encountered the symbol "RANDOM_INTERVAL" when expecting one of the following: — Pugzly, Dec 04 '22 at 13:04
You need to add a forward slash (/) at the end of the CREATE OR REPLACE FUNCTION statement. This is usually used to indicate the end of a SQL statement — DotNetRussell, Dec 04 '22 at 13:48
I replaced ELSE IF WITH ELSIF and I got it to compile. Thanks for your help. Curious can you tell me what version of oracle you ran this on as there seems to be an inconsistenty. — Pugzly, Dec 04 '22 at 14:49
This code should work with any version of Oracle from 11g onward. — DotNetRussell, Dec 04 '22 at 14:53

MT0 · Accepted Answer · 2022-12-04T22:26:24.113

1

You don't need a user-defined function as you can use the built-in functions DBMS_RANDOM.VALUE(lower_bound, upper_bound) and NUMTODSINTERVAL(amount, duration):

SELECT NUMTODSINTERVAL(
         DBMS_RANDOM.VALUE(1, 3),
         'MINUTE'
       )
FROM   DUAL;

Which will generate a random interval greater than or equal to 1 minute and less than 3 minutes (with a random about of seconds).

If you did want to wrap it into a function then:

CREATE FUNCTION random_interval(
  p_min      IN NUMBER,
  p_max      IN NUMBER,
  p_duration IN VARCHAR2
) RETURN INTERVAL DAY TO SECOND
IS
BEGIN
  RETURN NUMTODSINTERVAL(DBMS_RANDOM.VALUE(p_min, p_max), p_duration);
END;
/

If you want the seconds to be an integer then:

CREATE OR REPLACE FUNCTION random_interval(
  p_min      IN NUMBER,
  p_max      IN NUMBER,
  p_duration IN VARCHAR2
) RETURN INTERVAL DAY TO SECOND
IS
  v_interval INTERVAL DAY TO SECOND := NUMTODSINTERVAL(DBMS_RANDOM.VALUE(p_min, p_max), p_duration);
BEGIN
  RETURN ( EXTRACT(DAY FROM v_interval) * 24 * 60 * 60
         + EXTRACT(HOUR FROM v_interval) * 60 * 60
         + EXTRACT(MINUTE FROM v_interval) * 60
         + FLOOR(EXTRACT(SECOND FROM v_interval))
         ) * INTERVAL '1' SECOND;
END;
/

fiddle

edited Dec 04 '22 at 22:26

answered Dec 04 '22 at 22:14

MT0

143,790
11
59
117

MTO Your solution is nice, neat and concise. Is there a way to combine both your solutions into a single function as there are times I need to add and INTERVAL to a TIMESTAMP and times just to a date. I would like to have all this functionality in one function so its easier to maintain. Thanks for your time, effort, patience and expertise!!!! – Pugzly Dec 05 '22 at 05:09
@Pugzly You do not need to do anything as you can add an `INTERVAL` to a `DATE` or `TIMESTAMP` or `TIMESTAMP WITH TIME ZONE` and it will work. – MT0 Dec 05 '22 at 08:16
@Pugzly "Is there a way to combine both your solutions into a single function" I'm not sure I understand the question as the first query is how to do it without a user-defined function using only built-in functions; the second query is exactly the same solution as the first but wrapped in a user-defined function for convenience; and the third takes the second solution and extends it to round the seconds component down to whole seconds. Each solution builds on the previous solution so there should be nothing that needs to be combined; just pick the one with your required functionality. – MT0 Dec 05 '22 at 10:21

Generating random INTERVAL

2 Answers2