In Oracle, how does one return the time interval between two dates?

Source: Internet
Author: User

We know that in SQL Server, datediff can return the time interval between two dates, but such a function does not exist in Oracle. How can this problem be achieved? We will discuss the problem in two ways:
1. Just roughly return the number of days
We know that in Oracle, two dates subtract from each other and return a different number of days. Of course, this number of days is not necessarily an integer. It converts the time, minute, and second of the two dates into days.
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
 
In this case, we can use the trunc function to obtain the number of days we want:
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
 
Of course, you can also do this:
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. Return the exact interval
For example, if I want to get two dates separated by several days, several hours and several seconds, then trunc cannot be used. We can use numtodsinterval and extract to achieve this:
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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.