Re: Bug in JDBC-Driver?

I haven't been closely following this thread so I may be completely off
base here.  But wouldn't having both java and the server using the same
known timezone have the same effect?  So if you were to set the timezone
to GMT in both the client and server, timestamps could then be passed
correctly.

--Barry

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Kris Jurka
Sent: Friday, December 17, 2004 9:16 PM
To: Antje.Stejskal@ppi.de
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Bug in JDBC-Driver?



On Tue, 30 Nov 2004, Kris Jurka wrote:

> 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.

The solution to this requires that the data sent to the server is
already
in the server's timezone, so that when it converts to the server's
timezone nothing happens.  There are two ways to make this
happen, for the driver to be aware of the server's timezone and
modify the data to match before sending, or to force the server's
timezone to be that of the client.

The first approach is hard because it is difficult to ascertain the
server's timezone (pre 8.0).  In 7.4 SHOW timezone; usually gives
"unknown".  You could try doing things like

SELECT now() - now() AT TIME ZONE 'UTC';

but that only gives you the offset on the current date, not an arbitrary

date.

The second approach forces the server timezone to be the JVM's via the
pseudo sql/java:

SET TIMEZONE = java.util.TimeZone.getDefault().getID();

This is a little more intrusive on the server side, but it should not be

visible using standard JDBC calls for date and time information.  The
attached patch implements this and I plan to apply it unless someone has
a
better idea or a reason it won't work.

Kris Jurka