I'm currently in the process of migrating some data from oracle to postgres. I was doing some basic comparisons between row counts and I'm founding a problem in one of the queries. First up, all the data has been successfully migrated from Oracle to Postgres where the row counts of all tables match. After that, I was generating some json files which were being generated on some select statements and this is where I found differences between the json files generated using Oracle vs the ones generated using Postgres.
The below is a Postgres query which generates one of the json files
SELECT * FROM some_table WHERE recurring_end_dt >= CURRENT_DATE
And the below is the query from Oracle
SELECT * FROM some_table WHERE recurring_end_dt >= sysdate
Just so you know, the number of rows in that table for both the databases is the same. The null counts in that column for both databases is also same.
The row count for postgres is 14700 while that for Oracle is 14400. Since there are 300 extra rows in Postgres, I tried to understand what the value for the recurring end_date is for those 300 rows. I found that they match exactly with the value in Oracle. For example, the value in Postgres is 2023-12-07 while that in Oracle for the same primary key is 07-DEC-23. Yet, the row from Oracle doesn't get displayed as part of the result of the query even when they should. So is that a problem in Oracle or Postgres since technically Postgres is returning the correct query
In Oracle, a
DATE is a binary data-type that consists of 7 bytes representing century, year-of-century, month, day, hour, minute and second. It ALWAYS contains those components and it is NEVER stored in any particular human-readable format.
For example, the value in Postgres is 2023-12-07 while that in Oracle for the same primary key is 07-DEC-23.
No, it is not. The value in Oracle is displayed by the client application you are using as
07-DEC-23 but the value is not
07-DEC-23, it will be a binary value with both date and time components and the time component is not being displayed.
Your client application is probably using the default
NLS_DATE_FORMAT session parameter to format how it displays dates; you can change the
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Then you will see that
SYSDATE will have a time component and so will the dates in your table (if you have not set explicitly them then they will default to midnight).
So is that a problem in Oracle or Postgres since technically Postgres is returning the correct query
It is not a problem with either, your queries are asking different things.
If you want values from today at midnight (and not from the current instant) then use:
SELECT * FROM some_table WHERE recurring_end_dt >= TRUNC(sysdate)