I have a function below, which works fine and generates a random date.

I'm trying to pair a start_date with and end_date for the same row. Is there a mechanism I can use in SQL where I can add random intervals hours, minutes, seconds to the start_date to ensure the end_date is at least 1 hr greater and less than 10 hours than the start_date?

For example, say I want to populate the table below. I am open to using a function, package type if need be.

Thanks in advance to all who respond.

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 TABLE t1 (     
 seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
   start_date   DATE,
   end_date    DATE 
);

/* here I need help with end_date */

 INSERT INTO t1 (start_date, end_date)
        SELECT
            random_date(DATE '2022-04-01', DATE '2022-04-30'),
 FROM dual CONNECT BY level <= 50;

CodePudding user response：

The way I see it, one query won't do because you have to check whether difference of randomly fetched start and end date match criteria. Therefore, a PL/SQL block which - in a loop until counter hits the limit of 50 rows (taken from your connect by clause) - validates dates and inserts a row only if difference is between 1 and 10 hours:

SQL> DECLARE
  2     l_cnt         NUMBER := 0;
  3     l_start_date  DATE;
  4     l_end_date    DATE;
  5  BEGIN
  6     WHILE l_cnt < 50
  7     LOOP
  8        SELECT random_date (DATE '2022-04-01', DATE '2022-04-30') start_date,
  9               random_date (DATE '2022-04-01', DATE '2022-04-30') end_date
 10          INTO l_start_date, l_end_date
 11          FROM DUAL;
 12
 13        IF l_end_date - l_start_date BETWEEN 1 / 24 AND 10 / 24
 14        THEN
 15           INSERT INTO test (start_date, end_date)
 16                VALUES (l_start_date, l_end_date);
 17
 18           l_cnt := l_cnt   SQL%ROWCOUNT;
 19        END IF;
 20     END LOOP;
 21  END;
 22  /

PL/SQL procedure successfully completed.

SQL>

Result:

SQL> SELECT seq_num,
  2         start_date,
  3         end_date,
  4         ROUND ((end_date - start_date) * 24, 1) diff_hours
  5    FROM test;

   SEQ_NUM START_DATE       END_DATE         DIFF_HOURS
---------- ---------------- ---------------- ----------
         1 08.04.2022 11:14 08.04.2022 18:49        7,6
         2 14.04.2022 19:23 14.04.2022 23:06        3,7
         3 13.04.2022 12:12 13.04.2022 16:06        3,9
         4 24.04.2022 13:04 24.04.2022 16:03          3
         5 01.04.2022 12:52 01.04.2022 19:29        6,6
         6 08.04.2022 08:24 08.04.2022 12:56        4,5
         7 29.04.2022 22:05 29.04.2022 23:22        1,3
         8 20.04.2022 05:35 20.04.2022 10:31        4,9
         9 23.04.2022 00:52 23.04.2022 08:46        7,9
        <snip>
        46 05.04.2022 20:06 06.04.2022 01:52        5,8
        47 11.04.2022 20:03 12.04.2022 05:11        9,1
        48 12.04.2022 17:07 12.04.2022 23:13        6,1
        49 30.04.2022 09:32 30.04.2022 13:42        4,2
        50 12.04.2022 00:24 12.04.2022 10:19        9,9

50 rows selected.

SQL>

CodePudding user response：

You could add a function to generate a random interval within the 1 and 10 hour limits, and add that to the start date - which would need an inline view or CTE as you need to refer to the random start date twice to do the calculation:

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

and then

INSERT INTO t1 (start_date, end_date)
WITH cte (start_date, duration) AS (
    SELECT
        random_date(DATE '2022-04-01', DATE '2022-04-30'),
        random_interval(1, 10)
    FROM dual CONNECT BY level <= 50
 )
SELECT start_date, start_date   duration
FROM cte;

db<>fiddle

In recent versions of Oracle you don't need either permanent function, if this is all they will be used for; you can define them within the WITH clause:

INSERT /*  WITH_PLSQL */ INTO t1 (start_date, end_date)
WITH 
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;
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;
cte (start_date, duration) AS (
    SELECT
        random_date(DATE '2022-04-01', DATE '2022-04-30'),
        random_interval(1, 10)
    FROM dual CONNECT BY level <= 50
 )
SELECT start_date, start_date   duration
FROM cte;

db<>fiddle

My overall goal is to write a function that will RETURN a string denoting of NdaysNHoursNMinutesNSeconds which represents the difference from end_date-start_date

That isn't really anything to do with what you asked, but it's fairly simple if you treat the dates as timestamps, and extract the elements from the interval value you get from subtracting those:

create function diff_as_string (p_from date, p_to date)
return varchar2 is
  l_interval interval day(3) to second(0);
begin
  l_interval := cast(p_to as timestamp) - cast(p_from as timestamp);
  return extract (day from l_interval) || 'Days'
    || extract (hour from l_interval) || 'Hours'
    || extract (minute from l_interval) || 'Minutes'
    || extract (second from l_interval) || 'Seconds';
end diff_as_string;
/

At least as long as the elapsed time is less than 1000 days, which would exceed the range allowed by that interval type.

And again you could define that function in a WITH clause instead; or it could be done just as a query, maybe with a normal CTE or inline view to avoid repeatedly converting the data:

select seq_num, start_date, end_date,
  extract (day from diff_interval) || 'Days'
    || extract (hour from diff_interval) || 'Hours'
    || extract (minute from diff_interval) || 'Minutes'
    || extract (second from diff_interval) || 'Seconds' as diff
from (
  select seq_num, start_date, end_date,
    cast(end_date as timestamp) - cast(start_date as timestamp) as diff_interval
  from t1
)
order by seq_num;

db<>fiddle with and without a function.