PreparedStatement.setDate() behavior with OVERLAPS

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

Responses

Browse pgsql-jdbc by date

  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