Oracle中如何返回两个日期之间的时间间隔？

关注

原创最新推荐文章于 2026-07-09 18:25:49 发布 1.1w 阅读 AI 写同款· GEO 优化 ›

UG\NX二次开发替换部件引用集UF_ASSEM_replace_refset 替换部件引用集UF_ASSEM_replace_refset，需要替换成“完整部件（Entire Part）”需要第三个参数传入空指针或者空字符串。阅读详情

我们知道在SQL SERVER中有datediff可以返回两个日期之间的时间间隔，但在Oracle中却不存在这样的函数，那要怎么实现呢？我们分两种情况讨论：
1.只是粗略返回相隔的天数
我们知道在Oracle中两个日期相减返回的就是相差的天数，当然这个天数未必是整数，它会把两个日期相差的时分秒折算成天数
SQL> SELECT SYSDATE-HIREDATE FROM EMP;
SYSDATE-HIREDATE
----------------
      3.12123843
      9316.66291
      9314.66291
      9275.66291
      9096.66291
      9246.66291
      9207.66291
      9046.66291
      9116.66291
      9030.66291
      9030.66291
      8979.66291

此时我们可以用TRUNC函数去获取我们所要得到的天数：
SQL> SELECT SYSDATE-HIREDATE,TRUNC(SYSDATE-HIREDATE) FROM EMP;
SYSDATE-HIREDATE TRUNC(SYSDATE-HIREDATE)
---------------- -----------------------
      3.12486111                       3
      9316.66653                    9316
      9314.66653                    9314
      9275.66653                    9275
      9096.66653                    9096
      9246.66653                    9246
      9207.66653                    9207
      9046.66653                    9046
      9116.66653                    9116
      9030.66653                    9030
      9030.66653                    9030
      8979.66653                    8979

当然也可以这样：
SQL> SELECT SYSDATE-HIREDATE,TRUNC(SYSDATE,'DDD')-TRUNC(HIREDATE,'DDD') FROM EMP;
SYSDATE-HIREDATE TRUNC(SYSDATE,'DDD')-TRUNC(HIREDATE,'DDD')
---------------- ------------------------------------------
      3.12626157                                          3
      9316.66793                                       9316
      9314.66793                                       9314
      9275.66793                                       9275
      9096.66793                                       9096
      9246.66793                                       9246
      9207.66793                                       9207
      9046.66793                                       9046
      9116.66793                                       9116
      9030.66793                                       9030
      9030.66793                                       9030
      8979.66793                                       8979

2.返回精确的时间间隔
比如我要得到两个日期相隔几天几小时几分几秒，这时就不能用TRUNC来实现了，我们可以用NUMTODSINTERVAL和EXTRACT来实现：
SQL> SELECT EXTRACT(DAY FROM NUMTODSINTERVAL(SYSDATE-HIREDATE,'DAY')) DAY,
2         EXTRACT(HOUR FROM NUMTODSINTERVAL(SYSDATE-HIREDATE,'DAY')) HOUR,
3         EXTRACT(MINUTE FROM NUMTODSINTERVAL(SYSDATE-HIREDATE,'DAY')) MINUTE,
4         NUMTODSINTERVAL(SYSDATE-HIREDATE,'DAY') DETAIL
5 FROM EMP;
       DAY       HOUR     MINUTE DETAIL
---------- ---------- ---------- ------------------------------
         3          3           31 +000000003 03:31:54.999999999
      9316         16         31 +000009316 16:31:55.000000000
      9314         16         31 +000009314 16:31:55.000000000
      9275         16         31 +000009275 16:31:55.000000000
      9096         16         31 +000009096 16:31:55.000000000
      9246         16         31 +000009246 16:31:55.000000000
      9207         16         31 +000009207 16:31:55.000000000
      9046         16         31 +000009046 16:31:55.000000000
      9116         16         31 +000009116 16:31:55.000000000
      9030         16         31 +000009030 16:31:55.000000000
      9030         16         31 +000009030 16:31:55.000000000
      8979         16         31 +000008979 16:31:55.000000000