Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'
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;
/
SELECT dt,
       COUNT(*) AS cnt
FROM   (
  SELECT ROWNUM AS rn,
         random_date(DATE '1970-01-01', DATE '1970-01-01' + INTERVAL '5' SECOND) AS dt
  FROM   DUAL
  CONNECT BY LEVEL <= 1e5
)
GROUP BY dt;


DT
CNT


1970-01-01 00:00:03
16662

1970-01-01 00:00:04
16367

1970-01-01 00:00:01
16802

1970-01-01 00:00:05
16771

1970-01-01 00:00:02
16724

1970-01-01 00:00:00
16674


CREATE FUNCTION random_timestamp(
  p_from IN TIMESTAMP,
  p_to   IN TIMESTAMP
) RETURN TIMESTAMP
IS
  c_from CONSTANT TIMESTAMP := LEAST(p_from, p_to);
  c_to   CONSTANT TIMESTAMP := GREATEST(p_from, p_to);
BEGIN
  RETURN c_from + DBMS_RANDOM.VALUE()
                  * (c_to + INTERVAL '0.000000001' SECOND - c_from);
END random_timestamp;
/
SELECT ts,
       COUNT(*) AS cnt
FROM   (
  SELECT ROWNUM AS rn,
         random_timestamp(
           TIMESTAMP '1970-01-01 00:00:00.000005',
           TIMESTAMP '1970-01-01 00:00:00.000000'
         ) AS ts
  FROM   DUAL
  CONNECT BY LEVEL <= 1e5
)
GROUP BY ts;


TS
CNT


1970-01-01 00:00:00.000003000
20179

1970-01-01 00:00:00.000004000
20006

1970-01-01 00:00:00.000000000
9924

1970-01-01 00:00:00.000001000
19935

1970-01-01 00:00:00.000002000
19937

1970-01-01 00:00:00.000005000
10019


SELECT random_timestamp(DATE '-4712-01-01', DATE '9999-12-31')
FROM  DUAL;


RANDOM_TIMESTAMP(DATE'-4712-01-01',DATE'9999-12-31')


8057-06-02 18:41:25.492676000


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;
/
SELECT * FROM USER_ERRORS;
SELECT ts,
       COUNT(*) AS cnt
FROM   (
  SELECT ROWNUM AS rn,
         random_timestamp(
           TIMESTAMP '1970-01-01 00:00:00.000005',
           TIMESTAMP '1970-01-01 00:00:00.000000'
         ) AS ts
  FROM   DUAL
  CONNECT BY LEVEL <= 1e5
)
GROUP BY ts;


TS
CNT


1970-01-01 00:00:00.000002000
16589

1970-01-01 00:00:00.000000000
16830

1970-01-01 00:00:00.000005000
16501

1970-01-01 00:00:00.000001000
16867

1970-01-01 00:00:00.000004000
16480

1970-01-01 00:00:00.000003000
16733


SELECT random_timestamp(DATE '-4712-01-01', DATE '9999-12-31')
FROM  DUAL;


RANDOM_TIMESTAMP(DATE'-4712-01-01',DATE'9999-12-31')


4569-03-25 02:35:57.013431000

DT	CNT
1970-01-01 00:00:03	16662
1970-01-01 00:00:04	16367
1970-01-01 00:00:01	16802
1970-01-01 00:00:05	16771
1970-01-01 00:00:02	16724
1970-01-01 00:00:00	16674

TS	CNT
1970-01-01 00:00:00.000003000	20179
1970-01-01 00:00:00.000004000	20006
1970-01-01 00:00:00.000000000	9924
1970-01-01 00:00:00.000001000	19935
1970-01-01 00:00:00.000002000	19937
1970-01-01 00:00:00.000005000	10019

TS	CNT
1970-01-01 00:00:00.000002000	16589
1970-01-01 00:00:00.000000000	16830
1970-01-01 00:00:00.000005000	16501
1970-01-01 00:00:00.000001000	16867
1970-01-01 00:00:00.000004000	16480
1970-01-01 00:00:00.000003000	16733