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?

You fell prey to EDB's marketing claim that their fork is a drop-in replacement for Oracle. — Laurenz Albe, Jun 03 '22 at 05:56
This Doubt is cleared but have another doubt mentioned here! https://stackoverflow.com/questions/72487836/oracle-and-postgres-results-different — anant, Jun 03 '22 at 10:25

Littlefoot · Accepted Answer · 2022-06-03T05:48:40.160

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.

Also can i subtract the 2 dates in postgres without Timestamp? — anant, Jun 03 '22 at 05:48
Like sv=> select TO_DATE('30-12-1899','dd-mm-yyyy'); to_date -------------------- 30-DEC-99 00:00:00 (1 row) Return with timestamp! And in case of oracle it returns wihtout timestamp! — anant, Jun 03 '22 at 05:49
Can someone Address this doubt as well? https://stackoverflow.com/questions/72487836/oracle-and-postgres-results-different — anant, Jun 03 '22 at 10:24

Date Difference in Oracle And EDB (Postgres)

1 Answers1

Linked

Related