From: | Christopher BROWN <brown(at)reflexe(dot)fr> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | PreparedStatement.setDate() behavior with OVERLAPS |
Date: | 2015-08-11 13:33:14 |
Message-ID: | CAHL_zcMr0u+xdhGM5vJvt6zTfOGdh9Cp1Pd+gEERe6XC2khMvA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
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).
Thanks,
Christopher
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2015-08-11 14:01:28 | Re: PreparedStatement.setDate() behavior with OVERLAPS |
Previous Message | Bosco Rama | 2015-08-10 17:14:35 | Re: Re: Postgresql 9.4.4 - ERROR: invalid byte sequence for encoding "UTF8": 0x92 |