Date Difference in Oracle And EDB (Postgres)

Question

In case of Oracle the Query

select SYSDATE - TO_DATE('23-03-1022','dd-mm-yyyy') from dual;

is Giving the Output

SYSDATE-TO_DATE('23-03-1022','DD-MM-YYYY')
------------------------------------------
                                21715.2233

In case of EDB/Postgres it is-

select SYSDATE - TO_DATE('23-03-1022','dd-mm-yyyy') as date;

      date
---------------------
 21715 days 05:23:13
(1 row)

How can i get the Same output as oracle in case Of Postgres ?

What does .2233 tells in case of Oracle?

Answer 1

In Oracle, SYSDATE is a function that returns both date and time component. Difference of two DATE datatype values returns number of days between them. Therefore, as SYSDATE also contains hours, minutes and seconds, you'll almost always get a decimal number as the result (unless you run that piece of code at midnight).

Therefore, I presume that you'd - actually - want to truncate SYSDATE which then "removes" time component (sets it to 00:00:00):

SQL> select SYSDATE - TO_DATE('30-12-1899','dd-mm-yyyy') from dual;

SYSDATE-TO_DATE('30-12-1899','DD-MM-YYYY')
------------------------------------------
                                44715.3208

SQL> select trunc(SYSDATE) - TO_DATE('30-12-1899','dd-mm-yyyy') from dual;

TRUNC(SYSDATE)-TO_DATE('30-12-1899','DD-MM-YYYY')
-------------------------------------------------
                                            44715

SQL>

---

As of 0.2279: it is - as I said - number of days. To quickly demonstrate it: if you want to get number of hours, you'd multiply it by 24 (the rest by 60 to get minutes, and its rest by 60 again to get seconds):

SQL> select 0.2279 * 24 as hours from dual;

     HOURS
----------`enter code here`
    5.4696

SQL> select 0.4696 * 60 as minutes from dual;

   MINUTES
----------
    28.176

SQL> select 0.176 * 60 as seconds from dual;

   SECONDS
----------
     10.56

SQL>

Which means that you ran that query today at 05:28:10.