Generating random INTERVAL-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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