Oracle函数返回值之间的随机日期和时间戳

Question

我在创建一个返回随机日期的函数时遇到了一些困难(接下来是一个返回随机时间戳的函数)，当传入从日期和到日期范围时。

我希望有值也包括与日期相关的随机时间。对于时间戳函数，一个随机的小数值。

下面是我到目前为止为DATE函数所做的，但我似乎无法让它编译。任何帮助都将非常感激。感谢所有回答的人。

CREATE OR REPLACE FUNCTION random_date(
p_from IN DATE,
p_to   IN DATE
)
RETURN date DETERMINISTIC
IS
v_start DATE := TRUNC(LEAST(p_from, p_to));
v_end   DATE := TRUNC(GREATEST(p_from, p_to));
RETURN  p_from
+ DBMS_RANDOM.VALUE(0, p_to -  p_from + 1);
END random_date;
/

Answer 1

It:

• 缺少BEGIN关键字;
• 不希望它是DETERMINISTIC(因为它不会):

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

对于TIMESTAMPs:

CREATE FUNCTION random_timestamp(
p_from IN TIMESTAMP,
p_to   IN TIMESTAMP
) RETURN TIMESTAMP
IS
c_from CONSTANT TIMESTAMP(9) := LEAST(p_from, p_to);
c_to   CONSTANT TIMESTAMP(9) := GREATEST(p_from, p_to);
BEGIN
RETURN c_from + DBMS_RANDOM.VALUE()
* (c_to + INTERVAL '0.000000001' SECOND - c_from);
END random_timestamp;
/

注意:DBMS_RANDOM.VALUE()将返回一个大于或等于0且小于1的值，因此如果您想将p_to绑定在范围内，则需要以尽可能小的增量增加范围;因此为日期添加1秒，为时间戳添加1 -9秒。

---

上面的RANDOM_TIMESTAMP函数可以工作，但不是真正的随机，因为由于舍入问题，范围两端的瞬间发生的概率是两个极端之间所有其他瞬间的一半。对于大多数情况，这不是一个特别的问题，但如果是(特别是对于几微秒的小范围)，那么您需要在应用随机性之前将间隔的持续时间转换为整数:

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

db<此处小提琴>

Answer 2

我无法为范围的最后一天生成日期或时间戳，无论它落在这个月的哪个位置。例如，random_date (DATE '2022-04-27'， DATE '2022-05-03')生成一个月的最后一天(4月30日)的日期，但不生成5月3日的日期。

如果p_from是2022年4月1日的午夜，p_to是2022年4月30日的午夜，那么我需要生成一个介于0到30之间的随机数，但是(p_to - p_from) = 29，而不是30。(想想看;如果p_to也是2022年4月1日的午夜，那么(p_to - p_from)将是0，而不是1。)如果我想要一个小于(不等于)5月1日午夜的随机日期，那么将5月1日作为第二个参数传递给random_date。为了自动完成此操作，我对函数做了以下更改:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';
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 OR REPLACE FUNCTION random_timestamp(
p_from IN TIMESTAMP,
p_to   IN TIMESTAMP
) RETURN TIMESTAMP
IS
BEGIN
RETURN p_from + DBMS_RANDOM.VALUE() * (p_to - p_from + interval '1' day);
END random_timestamp;
/

CREATE TABLE t1 (     
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
dt   DATE,
ts TIMESTAMP 
);
INSERT INTO t1 (dt, ts )
SELECT
random_date(DATE '2022-04-01', DATE '2022-04-30'),
random_timestamp(DATE '2022-04-01', DATE '2022-04-30')
FROM
dual CONNECT BY level <= 10000;

select trunc(dt), count(*)
from t1
group by trunc(dt)
Order by trunc(dt);
select trunc(ts), count(*)
from t1
group by trunc(ts)
Order by trunc(ts);