On Mon, 29 Nov 2004 Antje.Stejskal@ppi.de wrote:
> I am facing a time problem with timestamp handling. Here is what I did: I
> insert data via JDBC. The timestamp value in the prepared statement is still
> '2003-08-19 11:40:08.0' as the debugger (of Eclipse) confirms. Inserted in
> the database gets a tiemstamp 2003-08-19 09:40:08.0'. CURRENT_TIME of
> DB-Server is an hour back to server time.
> I used the JDBC-Driver pg80b1.308.jdbc3.jar
>
> Today I tried my program using the JDBC-Driver of Postgres 7.4.6
> (pg74.215.jdbc3.jar). With that JDBC- driver everything works fine. Is there
> any parameter I missed to set in version 8, or is it a bug?
>
Yes, this looks like a driver bug, but I don't see an easy way to get
around it. The problem arises from the fact that you are using a
timestamp without time zone and the 8.0 driver using the V3 protocol types
all java.sql.Timestamp objects as timestamp with time zone. In a -08
timezone note:
Just discards the zone information
jurka=# SELECT '2004-11-29 22:09:59.079-09'::timestamp;
timestamp
-------------------------
2004-11-29 22:09:59.079
(1 row)
Converts to the server's zone and then discards.
jurka=# SELECT '2004-11-29 22:09:59.079-09'::timestamptz::timestamp;
timestamp
-------------------------
2004-11-29 23:09:59.079
(1 row)
So you by going through the intermediate with time zone type the value is
changed. For your situation you can just switch to a column type that has
zone information. To fix this in the driver it seems we would need to be
aware of the database server's timezone and adjust data going in one
direction or the other.
The attached test case illustrates the problem by setting up the server to
be in US Eastern and the java client to be in US Pacific timezones and
comparing the results when running with the V2/V3 protocols.
Kris Jurka
