TIMESTAMP范围异常求助：随机时间戳生成函数超出指定区间

问题原因 #

1. 时间范围错误扩大：原函数计算时间偏移时，错误地在p_to - p_from的基础上加了INTERVAL '1' DAY，导致可用的时间差值被额外增加了一整天。以你的测试案例为例，原本合法的时间差是3小时，加1天后变成27小时，乘以dbms_random.value()返回的0~1随机数后，最大偏移量接近27小时，加到起始时间2023-01-25 09:00:00就会得到次日的时间，直接超出结束时间上限。
2. 无参数合法性校验：当传入的p_from晚于p_to时，时间差为负数，乘以随机数后会得到负偏移量，最终结果会早于起始时间，这就是你遇到“有时返回值早于起始时间”的原因。

修复方案 #

移除多余的日期间隔，增加参数校验，优化小数部分处理逻辑，修复后的函数如下：

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';

CREATE OR REPLACE FUNCTION random_timestamp(
      p_from      IN TIMESTAMP,
      p_to        IN TIMESTAMP,
      p_fraction  IN VARCHAR2   DEFAULT 'Y'
    ) RETURN TIMESTAMP
   IS
      v_time_diff INTERVAL DAY(9) TO SECOND(9);
      return_val_y TIMESTAMP;
      return_val_n TIMESTAMP(0);
 BEGIN
      -- 校验输入参数，确保起始时间不晚于结束时间
      IF p_from > p_to THEN
          RAISE_APPLICATION_ERROR(-20001, '起始时间不能晚于结束时间');
      END IF;

      v_time_diff := p_to - p_from;
      -- 生成区间内的随机时间戳
      return_val_y := p_from + dbms_random.value() * v_time_diff;
      -- 截断小数部分到秒级
      return_val_n := CAST(return_val_y AS TIMESTAMP(0));

      RETURN  CASE
                  WHEN UPPER(SUBSTR(p_fraction, 1, 1)) = 'Y'
                  THEN return_val_y
                  ELSE return_val_n
              END;
END random_timestamp;
/

验证测试 #

运行你的测试案例：

SELECT random_timestamp( 
TIMESTAMP '2023-01-25 09:00:00', TIMESTAMP '2023-01-25 12:00:00') as ts from dual

现在返回的结果会严格落在2023-01-25 09:00:00到2023-01-25 12:00:00区间内，不会出现超出范围的情况。

内容的提问来源于stack exchange，提问作者Beefstu