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

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

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

Christopher Hunt wrote:
> 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?

I don't really have an explanation, but I would recommend using
setTimestamp instead of setString for timestamp values.

Maybe there's a time or timezone difference between client and server,
and in the former case the server setting is used, and in the latter,
the client setting?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

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

Thanks for responding.

Alas, I have tried setTimestamp - in fact I started with that and
ended up with a similar problem i.e. no rows returned with
setTimestamp. I think that my problem is something else.

The client and server processes are on the same machine.

Any more ideas?

Cheers,
-C

Christopher,

Use a more recent jar. There is a problem with the spec, and
postgresql. The spec does not have two different timestamps. In
previous versions we bound the type to timestamp without timezone. In
more recent versions we bind it to 'unknown' which allows the server
to decide.

If this fails then appending ?protocolVersion=2 will work, however
you won't be server side prepared statements.

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

> Thanks for responding.
>
> Alas, I have tried setTimestamp - in fact I started with that and
> ended up with a similar problem i.e. no rows returned with
> setTimestamp. I think that my problem is something else.
>
> The client and server processes are on the same machine.
>
> Any more ideas?
>
> Cheers,
> -C
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

Thanks!

On 18/06/2007, at 11:33 PM, Dave Cramer wrote:

> Christopher,
>
> Use a more recent jar. There is a problem with the spec, and
> postgresql. The spec does not have two different timestamps. In
> previous versions we bound the type to timestamp without timezone.
> In more recent versions we bind it to 'unknown' which allows the
> server to decide.
>
> If this fails then appending ?protocolVersion=2 will work, however
> you won't be server side prepared statements.

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

Kris Jurka <books(at)ejurka(dot)com> writes:
> 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)

BTW, as of 8.3 this particular foot-gun will go away:

regression=# select now() < 'a'::text;
ERROR: operator does not exist: timestamp with time zone < text
LINE 1: select now() < 'a'::text;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

You'll still be able to force a textual comparison if you really want
one:

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

but it won't happen silently.

regards, tom lane