Re: Literal vs parameterized 'timestamp with time zone' value

On Mon, 18 Jun 2007, Christopher Hunt wrote:

> This one has been baffling me for several hours so I hope that the list can
> help.
>
> I'm having an awful difficulty specifying a parameter in a select where
> clause of a prepared statement.
>
> Please consider this schema:
>
>         Table "public.moving_object_status"
>   Column    |            Type             | Modifiers
> --------------+-----------------------------+-----------
> validtime    | timestamp(2) with time zone | not null
> Indexes:
>   "moving_object_status_validtime" btree (validtime)
>
> (other columns removed for brevity).
>
> If I create the following prepared statement:
>
> PreparedStatement sqlStatement =
> sqlConnection.prepareStatement("select validtime from moving_object_status
> where validtime < '2005-06-08T20:05:45.825+0'");
>
> then 1 row is returned as I would expect given my dataset.
>
> However if I use a parameter:
>
> PreparedStatement sqlStatement =
> sqlConnection.prepareStatement("select validtime from moving_object_status
> where validtime < ?");
> int columnIndex = 1;
> sqlStatement.setString(columnIndex++, "2005-06-08T20:05:45.825+0");
>
> No rows are returned.
>
> Can anyone explain the difference in results given a literal value and a
> parameterised value in this context?
>

In the second example a string comparison is being done instead of a
timestamp comparison.  Consider this psql example:

jurka=# select now() < 'a'::text;
  ?column?
----------
  t
(1 row)

When you say "sqlStatement.setString" you are saying you have a string
variable and that's the way the server interprets it.  When you leave
something as a literal it gets typed as "unknown" by the server which then
infers by the comparison with timestamp that you want a timestamp:

jurka=# select now() < 'a';
ERROR:  invalid input syntax for type timestamp with time zone: "a"

So you really do need to say setTimestamp or use the drivers option to not
force binding of setString to a string type.  Why setTimestamp is not
working for you is probably timezone related, but without more details
I'm not sure how to help you track that down.

Kris Jurka