TO_DATE converted to TO_TIMESTAMP in ConvertMap_PostgreSQL.java

[Pritesh Shah]

Hello All,

In ConvertMap_PostgreSQL.java TO_DATE is converted to TO_TIMESTAMP. Does anyone knows reason for this?

Reason, I wanted to know is, i have query to Fact_Acct table having where condition to DateAcct column and DateAcct column has Fact_Acct table has index on DateAcct::date type. So in query when we TO_DATE to convert timestamp to date, internally it converts TO_DATE to TO_TIMESTAMP which won't hit the index on Fact_Acct table.

Thanks in advance!!

[Carlos Antonio Ruiz Gomez]

Hi Pritesh,

> In ConvertMap_PostgreSQL.java TO_DATE is converted to TO_TIMESTAMP.
> Does anyone knows reason for this?

Because oracle to_date is equivalent to postgresql to_timestamp


You can rewrite your query (making it specific for postgresql) to avoid
the convert:
- not using to_date - for example you could use cast or ::date notation
- using NATIVE_PostgreSQL_KEYWORKTO_DATE instead of the TO_DATE

Regards,

Carlos Ruiz

> --

[Pritesh Shah]

Hello Carlos,

Thank you for your quick response. I understood now how to use native keywords in case we need to use it. However with this conversion from to_date to to_timestamp for oracle, impacts the performance of queries to Fact_Acct table for DateAcct condition in case you are using postgressql database.

[Carlos Antonio Ruiz Gomez]

Checking in seed postgresql DB - fact_acct has two indexes, one for dateacct and another for trunc(dateacct)

What I see on FinReport.java is using trunc(dateacct) - not to_date

Regards,

Carlos Ruiz