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