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

On 18-Jun-07, at 8:34 AM, Christopher Hunt wrote:

> Hi there,
>
> 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");
>
I think you need to use setTimestamp here...  I'm actually surprised
it doesn't throw an exception.
> No rows are returned.
>
> Can anyone explain the difference in results given a literal value
> and a parameterised value in this context?
>
> Kind regards,
> Christopher
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that
> your
>       message can get through to the mailing list cleanly