Hello,
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).