Обсуждение: TimeZone related issues in org.postgresql.jdbc2.TimestampUtils

Hi All,

in an application that uses TIMESTAMP WITH TIME ZONE as the type for
certain attributes, we stumbled across the problem that, sometimes, the
values returned from the database will be adjusted so that the time zone
offset will be added/subtracted from the actual time stored in the
database.

Example: "2009-09-11 15:00:00+02" will be returned by TimestampUtils as
"2009-09-11 13:00:00".

This is due to the fact that in TimestampUtils.toTimestamp(...) and
TimestampUtils.toTime(...) the following will be used for returning the
Time or Timestamp instances, respectively:


toTimestamp(): Timestamp result = new Timestamp(useCal.getTime().getTime());

toTime():  Time result = new Time(useCal.getTime().getTime());

This however, leads to the aforementioned, incorrect results, since the
Calendar implementation, on getTime() will now adjust the initially
correct date and time based on the time zone information that is stored
with the data.

Whilst trying to work around that problem, I found out, that it would be
best to use

toTimestamp():
  DateFormat df = DateFormat.getDateTimeInstance();
  df.setTimeZone( useCal.getTimeZone() );
  Timestamp result = new Timestamp( df.parse( df.format( useCal.getTime()
) ).getTime() );

toTime():
  DateFormat df = DateFormat.getTimeInstance();
  df.setTimeZone( useCal.getTimeZone() );
  Time result = new Time( df.parse( df.format( useCal.getTime() )
).getTime() );

This then will lead to the correct results.

TIA for fixing this!

Best Regards

Carsten Klein


--

axn software UG (haftungsbeschränkt)
Wipperfürther Str. 278
51515 Kürten

HRB 66732
Gerichtsstand Amtsgericht Bergisch Gladbach

Telefon +492 268 801 285
Telefax +492 268 801 285
Mobil   +491 577 666 256 5

WWW http://www.axn-software.de
Email info@axn-software.de

Carsten Klein wrote:
> Hi All,
>
> in an application that uses TIMESTAMP WITH TIME ZONE as the type for
> certain attributes, we stumbled across the problem that, sometimes, the
> values returned from the database will be adjusted so that the time zone
> offset will be added/subtracted from the actual time stored in the
> database.

Timezone stuff is very picky. Can you provide a standalone testcase so
we can see exactly what's going on?

-O

I wonder what had happened to my reply to your inquiry for sending the
standalone test case?

Did you receive that?

If not, could you please inform me so that I can resend it by inlining the
test case. The last time I did send it as an attachment...

Regards

Carsten


> Carsten Klein wrote:
>> Hi All,
>>
>> in an application that uses TIMESTAMP WITH TIME ZONE as the type for
>> certain attributes, we stumbled across the problem that, sometimes, the
>> values returned from the database will be adjusted so that the time zone
>> offset will be added/subtracted from the actual time stored in the
>> database.
>
> Timezone stuff is very picky. Can you provide a standalone testcase so
> we can see exactly what's going on?
>
> -O
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>

Carsten Klein (tarent) wrote:
> Attached you find a stand alone test case. I have included a slightly
> modified version of the original TimestampUtils in the jdbc2 package of
> the PostgreSQL JDBC driver.

Do you have a standalone testcase that uses normal JDBC methods and
shows the problem as an application would see it.

TimestampUtils is an internal class that's not designed to be used
directly. Its behaviour is fairly closely tied to the server's timestamp
behaviour and exactly how the rest of the driver uses it.

What we are really interested in is a testcase that shows a problem at
the JDBC level.

-O