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(at)postgresql(dot)org so that
> your
> message can get through to the mailing list cleanly