Skip site navigation (1) Skip section navigation (2)

Literal vs parameterized 'timestamp with time zone' value

From: Christopher Hunt <huntc(at)internode(dot)on(dot)net>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Literal vs parameterized 'timestamp with time zone' value
Date: 2007-06-18 12:34:23
Message-ID: 094DD6D8-B500-479F-B730-0BFCC256A5DE@internode.on.net (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

Responses

pgsql-jdbc by date

Next:From: Christopher HuntDate: 2007-06-18 12:41:07
Subject: Re: Literal vs parameterized 'timestamp with time zone' value
Previous:From: EBIHARA, YuichiroDate: 2007-06-18 11:41:59
Subject: standard LOB support

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group