pushdown SYSDATE equivalent function

[legrandlegrand]

Hello, is there a way to perform queries with where conditions like: WHERE ORACLE_COL_DATE > NOW() - 2; or WHERE ORACLE_COL_DATE > NOW() - '2 day'::interval;

and having it pushed to Oracle as SYSDATE ?

maybe using SYSDATE() provided by orafce ...

without having to use literal like WHERE ORACLE_COL_DATE > '2016-04-26 21:30:00'::timestamp(0) - 2;

Thanks in advance for your answer

[laurenz]

When I wrote support for now(), I first considered Oracle SYSDATE (or rather SYSTIMESTAMP) as a replacement.

The problem with this approach is that it may return incorrect results, since

• there is no guarantee that the clocks on both databases are synchronized
• SYSDATE returns the current date and time set for the operating system on which the database server resides (see here), while now() will return values based on the start time of the current transaction (see here)

It is the design philosophy of oracle_fdw to only push down expressions that have the same semantics in Oracle and in PostgreSQL, so that the result stays the same (for example, I do not push down expressions that depend on character collation). This would not be guaranteed in this case.

Why do you want SYSDATE? oracle_fdw will push down expressions with now(), and the proper timestamp will be substituted as parameter:

test=> SELECT id FROM t1 WHERE enddate < now() - INTERVAL '2' DAY;
[...]
DEBUG:  oracle_fdw: remote query is: SELECT /*163869c2827412641ef5ad576a769974*/
            "ID", "ENDDATE" FROM "T1" WHERE
            ("ENDDATE" < ((CAST (:now AS TIMESTAMP WITH TIME ZONE))
                        - INTERVAL '2 00:00:00.000000' DAY(9) TO SECOND(6)))
[...]
DEBUG:  oracle_fdw: execute query in foreign table scan on 17738,
            parameters :now="2016-04-27 09:45:43.216077+02:00 AD"
[...]

Is that not good enough?

[legrandlegrand]

Oups, sorry I didn't noticed that NOW() was pushed down: and yes it should be enough for sure.

But first I'll have to get rid of bug ERROR: error executing query: OCIDateTimeFromText failed to convert parameter to be able to use NOW() as described ;o)

[laurenz]

As mentioned in #52, I could send you a binary.

[legrandlegrand]

thanks it works fine !