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

我们知道在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

  • 0
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值