PreparedStatement.setDate() behavior with OVERLAPS can cause "invalid input syntax for type interval" error message

[cbrown06]

When I use the following query as a PreparedStatement with the 9.4.1201 JDBC driver, using a 9.4.4 database, the SQL is rejected:

SELECT id, ctime, mtime, is_archived, ref_store, ref_supplier, period_begins, period_ends, received_by, received_on, received_qty, disposed_qty FROM store_delivery WHERE (period_begins, period_ends + interval '1 day') OVERLAPS (?, ? + interval '1 day') AND ref_store = ? ORDER BY period_begins, ctime

Specifically, with "invalid input syntax for type interval". I'm setting the first two parameters to java.sql.Date values, using "setDate" method of PreparedStatement (the third parameter is an integer, ex 4251). Given that I'm in the CEST timezone, that translates to:

SELECT id, ctime, mtime, is_archived, ref_store, ref_supplier, period_begins, period_ends, received_by, received_on, received_qty, disposed_qty FROM store_delivery WHERE (period_begins, period_ends + interval '1 day') OVERLAPS ('2015-06-27 +02:00:00' , '2015-09-06 +02:00:00' + interval '1 day') AND ref_store = 4251 ORDER BY period_begins, ctime

I can solve this by modifying my input string, such that the first two parameters are written as ?::date, ?::date (and it seems to truncate any positive or negative timezone offset). However, I'm curious about two aspects of this, and would appreciate an explanation if that's possible.

• if I'm using "setDate" (and not "setTimestamp"), why is a timezone offset being tacked onto the date string?
• if a string such as '2015-06-27' isn't understood as a date (it seems to be automatic with a lot of other SQL databases, but then again that doesn't mean the majority are correct...), why doesn't "setDate" automatically prepend "DATE" or append "::date"? In which cases would this be a problem (I'd like to understand instead of writing bad SQL).

The main issue is that I didn't expect to have any timezone information including when setting a Date value. The secondary issue is that I didn't expect to have to perform any explicit casting here, as I'm using "setDate" and not "setString", and as there are no such errors when using in other contexts (such as a query with BETWEEN ? AND ? used with setDate).

See also:
pgsql-jdbc mailing list archive: "PreparedStatement.setDate() behavior with OVERLAPS"

[davecramer]

fixed in PR#340