如何修改Oracle自定义datediff函数以显示TIMESTAMP的小数秒部分？

原函数的问题在于参数是DATE类型，传入TIMESTAMP时会被隐式转换为DATE，直接丢失小数秒信息。要同时兼容两种类型且保留TIMESTAMP的小数秒，你可以通过重载函数实现，既不破坏原有DATE类型的使用，又能支持TIMESTAMP的高精度差值计算。

修改后的函数代码 #

CREATE OR REPLACE FUNCTION datediff(p_from DATE, p_to DATE)
RETURN VARCHAR2 IS
  l_years    PLS_INTEGER;
  l_from     DATE;
  l_interval INTERVAL DAY(3) TO SECOND(0);
BEGIN
  l_years := TRUNC(MONTHS_BETWEEN(p_to, p_from)/12);
  l_from := ADD_MONTHS(p_from, l_years * 12);
  l_interval := (p_to - l_from) DAY(3) TO SECOND(0);
  RETURN l_years || ' Years '
    || 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 datediff;
/

-- 新增重载函数，专门处理TIMESTAMP类型
CREATE OR REPLACE FUNCTION datediff(p_from TIMESTAMP, p_to TIMESTAMP)
RETURN VARCHAR2 IS
  l_years    PLS_INTEGER;
  l_from     TIMESTAMP;
  l_interval INTERVAL DAY(3) TO SECOND(9);
  l_seconds  NUMBER;
BEGIN
  -- 基于日期部分计算年份差，MONTHS_BETWEEN兼容TIMESTAMP类型
  l_years := TRUNC(MONTHS_BETWEEN(p_to, p_from)/12);
  l_from := ADD_MONTHS(p_from, l_years * 12);
  -- 用高精度interval存储剩余差值，保留9位小数秒（Oracle TIMESTAMP最大精度）
  l_interval := (p_to - l_from) DAY(3) TO SECOND(9);
  
  l_seconds := EXTRACT(SECOND FROM l_interval);
  RETURN l_years || ' Years '
    || EXTRACT(DAY FROM l_interval) || ' Days '
    || EXTRACT(HOUR FROM l_interval) || ' Hours '
    || EXTRACT(MINUTE FROM l_interval) || ' Minutes '
    -- 格式化秒数，自动去除末尾多余的零
    || TO_CHAR(l_seconds, 'FM999999999.999999999') || ' Seconds';
END datediff;
/

测试示例 #

测试DATE类型输入 #

SELECT 
datediff(TO_DATE('1981-04-01 10:11:13','YYYY-MM-DD HH24:MI:SS'), 
TO_DATE('2022-04-03 17:48:09','YYYY-MM-DD HH24:MI:SS')) AS diff 
FROM DUAL;

输出结果：

41 Years 2 Days 7 Hours 36 Minutes 56 Seconds

测试TIMESTAMP类型输入 #

SELECT 
datediff(TO_TIMESTAMP('1981-04-01 10:11:13.551000000', 'YYYY-MM-DD HH24:MI:SS.FF'),
TO_TIMESTAMP('2022-04-03 17:48:09.878700000', 'YYYY-MM-DD HH24:MI:SS.FF')) AS diff 
FROM DUAL;

输出结果：

41 Years 2 Days 7 Hours 36 Minutes 56.3277 Seconds

关键说明 #

• 重载函数后，原有调用DATE类型的代码无需修改，同时新增的TIMESTAMP分支会自动处理高精度时间差。
• TIMESTAMP分支使用INTERVAL DAY(3) TO SECOND(9)，完全匹配Oracle TIMESTAMP的9位小数秒精度。
• 用TO_CHAR格式化秒数时，FM修饰符会自动去掉末尾多余的零，避免出现56.0000这类冗余显示。

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