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

I should quickly add that I'm using v.8.0.4 of Postgres and the
postgresql-8.2-505.jdbc3.jar

Cheers,
-C

On 18/06/2007, at 10:34 PM, 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");
>
> 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