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(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Kris Jurka
Sent: Friday, December 17, 2004 9:16 PM
To: Antje(dot)Stejskal(at)ppi(dot)de
Cc: pgsql-jdbc(at)postgresql(dot)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

On Mon, 20 Dec 2004, Barry Lind wrote:

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

I initially thought this was a great idea, but it breaks down when they
don't use a PreparedStatement and generate a query directly, then we can't
intervene and adjust things. The advantage of setting the server to the
client's timezone is that this will work as expected.

Kris Jurka

Kris Jurka wrote:

> I initially thought this was a great idea, but it breaks down when they
> don't use a PreparedStatement and generate a query directly, then we can't
> intervene and adjust things. The advantage of setting the server to the
> client's timezone is that this will work as expected.

What about the {ts ...} etc escapes for time/date values?
Aren't you meant to use those for the case where you have
literal date values in the query?

-O