Обсуждение: JDBC Driver and timezones

Hi,

I have a little problem regarding timezones in the JDBC driver.

I have a locally installed Postgres server (PG 8.4.3 on Windows XP), so there is no difference between the timezone of
theserver and the client application. 

When I run the following in psql:

select current_time, localtime;

I get the following output:

      timetz      |     time
-----------------+--------------
  11:15:30.464+02 | 11:15:30.464
(1 row)

which is fine.

Now when I run the same statement through JDBC :

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select current_time, localtime");
if (rs.next())
{
   System.out.println("current_time: " + rs.getTime(1).toString());
   System.out.println("localtime: " + rs.getTime(2).toString());
}

I get the following output:

current_time: 10:18:49
localtime: 11:18:49

My expectation would have been to get the same display, but the current_time is one our off, which seems to be a
problemwith the daylight savings time.I 

The timezone for my computer is setup correctly (the clock is showing the correct time, and apparently psql has the
correcttime as well) 

The display from Java only works if I explicitely set the timezone when starting the JVM using -Duser.timezone=GMT+2

Is this a Java/JDK problem, a Windows problem or a driver problem?
I'm using JDK 1.6.0_18


Regards
Thomas

Thomas Kellerer wrote:

> My expectation would have been to get the same display, but the
> current_time is one our off, which seems to be a problem with the
> daylight savings time.

The JDK and the postgres server use independent timezone databases.
Perhaps they just have different daylight savings rules.

-O

Oliver Jowett wrote on 18.05.2010 14:37:
> Thomas Kellerer wrote:
>
>> My expectation would have been to get the same display, but the
>> current_time is one our off, which seems to be a problem with the
>> daylight savings time.
>
> The JDK and the postgres server use independent timezone databases.
> Perhaps they just have different daylight savings rules.
>
Sounds a bit strange given the fact that the German DST rules haven't been changed for ages...

Regards
Thomas

Hi,

actually, your application server is not using the system's default
timezone, see the configuration of your server.

I actually have filed this in the past as either bug or enhancement.
Basically the problem lies within the PGSQL JDBC driver, which will use
the application server's timezone setting when interpreting the data in
the timezone enabled table attributes.

In order to overcome this problem, I have adjusted a db layer that we are
using in the OSS VerA.Web project so that it will use proxies for the most
relevant objects (resultset and so on) that then will just drop the
timezone information in the data received from the database, so that when
instantiating the datetime object in the application server, it will
automatically take over the configured timezone. That way, you will
experience no timeshift, whatsoever.

See

https://evolvis.org/scm/viewvc.php/tags/tarent-database-1.5.4verawebpl3/src/main/java/de/tarent/dblayer/engine/proxy/?root=tarentdatabase

for more information.


Regards,

Carsten

--

Carsten Klein
Mobil +491 577 666 256 5
carsten.klein@axn-software.de

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

Geschäftsführung Carsten Klein
HRB 66732, Gerichtsstand Amtsgericht Bergisch Gladbach
Steuernr. 204/5740/0835, USt-IdNr. DE 266 540 939

Telefon +492 268 801 285, Telefax +492 268 801 285
www.axn-software.de, info@axn-software.de

Carsten Klein wrote on 18.05.2010 23:35:

> actually, your application server is not using the system's default
> timezone, see the configuration of your server.

The timezone is correct. The DST information seems to be "broken".

Besides: I'm not using an application server. It's a Swing application that retrieves the data via JDBC

The output from my initial post was from a very simply main() class, only runs that single statement I posted in my
initialpost. 
Even then it only works when I manually set user.timezone=GMT+2

And for the test case the client application _and_ Postgres were running on the same physical machine. So the JVM (and
thusthe JDBC driver) and Postgres should use the same timezone information from my Windows. 

When I output the value of user.timezone (when not setting it manually) it does report the correct one: Europe/Berlin,
butfor some reason it does not apply the DST settings correctly. 

> In order to overcome this problem, I have adjusted a db layer that we are
> using in the OSS VerA.Web project so that it will use proxies for the most
> relevant objects (resultset and so on) that then will just drop the
> timezone information in the data received from the database, so that when
> instantiating the datetime object in the application server, it will
> automatically take over the configured timezone. That way, you will
> experience no timeshift, whatsoever.
>
> See
>
https://evolvis.org/scm/viewvc.php/tags/tarent-database-1.5.4verawebpl3/src/main/java/de/tarent/dblayer/engine/proxy/?root=tarentdatabase

Thanks for the link, but as I said: this is a Swing application that directly connects to the Postgres server.
But I'm curious: why didn't you simply change the timezone for the JVM running your appserver?

Regards
Thomas

Thomas Kellerer wrote:

> And for the test case the client application _and_ Postgres were running
> on the same physical machine. So the JVM (and thus the JDBC driver) and
> Postgres should use the same timezone information from my Windows.

The JVM has its own separate timezone database. It does not use the
OS-provided timezone data in general.

-O

Oliver Jowett, 19.05.2010 02:00:
> Thomas Kellerer wrote:
>
>> And for the test case the client application _and_ Postgres were
>> running on the same physical machine. So the JVM (and thus the JDBC
>> driver) and Postgres should use the same timezone information from my
>> Windows.
>
> The JVM has its own separate timezone database. It does not use the
> OS-provided timezone data in general.
>

Thanks.

Yes that's what I assume as well. But I'm still surprised the JVM doesn't apply the DST settings correctly (the
timezone*is* correct) 

Regards
Thomas

Thomas Kellerer, 19.05.2010 08:17:
>>> And for the test case the client application _and_ Postgres were
>>> running on the same physical machine. So the JVM (and thus the JDBC
>>> driver) and Postgres should use the same timezone information from my
>>> Windows.
>>
>> The JVM has its own separate timezone database. It does not use the
>> OS-provided timezone data in general.
>>
>
> Yes that's what I assume as well. But I'm still surprised the JVM
> doesn't apply the DST settings correctly (the timezone *is* correct)
>

Hmm, I just tested this and apparently my assumption is wrong (Java *is* using the correct DST setting)

When I run:

System.out.println("DST active: " + TimeZone.getDefault().useDaylightTime());
System.out.println("DST delta: " + TimeZone.getDefault().getDSTSavings());

it correctly shows me that the JDK knows that DST is active and that it should add one hour
(without setting a timezone when starting the JVM)

So either that information is not used by the JDK, or there is something going on in the driver.

Thomas

On Wed, 19 May 2010, Thomas Kellerer wrote:

> System.out.println("DST active: " + TimeZone.getDefault().useDaylightTime());
> System.out.println("DST delta: " + TimeZone.getDefault().getDSTSavings());
>
> it correctly shows me that the JDK knows that DST is active and that it
> should add one hour
> (without setting a timezone when starting the JVM)

I think the problem is that the Java specification for java.sql.Time
requires that the date portion be filled in with 1970-01-01.  So when
trying to determine the zone shift to apply when printing it out, it is
checking with that date instead of today's date as you are expecting.

When reading a timetz value in, we don't need to determine what the zone
shift is, it's explicit, so in this case we shift by the explicit DST
amount.  When printing it back out, the original zone offset has been
discarded and we shift by the non-DST amount because the date 1970-01-01
doesn't use DST.  I think we'd like to be able to override the
getTimezoneOffset value for the Time object, but there's no easy way to do
that without creating our own PgTime object.

Kris Jurka

Hello,

I guess the issue is a logical one.
java Time has basically no date associated and rs.getTime(n) hence does not expect a timezone information as with PG
current_time.
Maybe there is a hidden fallback to rs.getTime(n, Calendar) ?


HTH,

Marc Mamin



java Time:
A thin wrapper around the java.util.Date class that allows the JDBC API to identify this as an SQL TIME value. 
The Time  class adds formatting and parsing operations to support the JDBC escape syntax for time values.

!! The date components should be set to the "zero epoch" value of January 1, 1970 and should not be accessed.


-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Thomas Kellerer
Sent: Mittwoch, 19. Mai 2010 08:44
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] JDBC Driver and timezones

Thomas Kellerer, 19.05.2010 08:17:
>>> And for the test case the client application _and_ Postgres were
>>> running on the same physical machine. So the JVM (and thus the JDBC
>>> driver) and Postgres should use the same timezone information from my
>>> Windows.
>>
>> The JVM has its own separate timezone database. It does not use the
>> OS-provided timezone data in general.
>>
>
> Yes that's what I assume as well. But I'm still surprised the JVM
> doesn't apply the DST settings correctly (the timezone *is* correct)
>

Hmm, I just tested this and apparently my assumption is wrong (Java *is* using the correct DST setting)

When I run:

System.out.println("DST active: " + TimeZone.getDefault().useDaylightTime());
System.out.println("DST delta: " + TimeZone.getDefault().getDSTSavings());

it correctly shows me that the JDK knows that DST is active and that it should add one hour
(without setting a timezone when starting the JVM)

So either that information is not used by the JDK, or there is something going on in the driver.

Thomas


-- 
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Kris Jurka, 19.05.2010 09:27:
>> System.out.println("DST active: " +
>> TimeZone.getDefault().useDaylightTime());
>> System.out.println("DST delta: " +
>> TimeZone.getDefault().getDSTSavings());
>>
>> it correctly shows me that the JDK knows that DST is active and that
>> it should add one hour
>> (without setting a timezone when starting the JVM)
>
> I think the problem is that the Java specification for java.sql.Time
> requires that the date portion be filled in with 1970-01-01. So when
> trying to determine the zone shift to apply when printing it out, it is
> checking with that date instead of today's date as you are expecting.
>
> When reading a timetz value in, we don't need to determine what the zone
> shift is, it's explicit, so in this case we shift by the explicit DST
> amount. When printing it back out, the original zone offset has been
> discarded and we shift by the non-DST amount because the date 1970-01-01
> doesn't use DST. I think we'd like to be able to override the
> getTimezoneOffset value for the Time object, but there's no easy way to
> do that without creating our own PgTime object.
>

Thanks for the detailed answer.
For the time being I can workaround this by explicitely setting the timezone.

It might be worth mentioning in the driver's documentation though.

Regards
Thomas

Kris Jurka
>> I think the problem is that the Java specification for java.sql.Time
>> requires that the date portion be filled in with 1970-01-01. So when
>> trying to determine the zone shift to apply when printing it out, it is
>> checking with that date instead of today's date as you are expecting.
>>
>> When reading a timetz value in, we don't need to determine what the zone
>> shift is, it's explicit, so in this case we shift by the explicit DST
>> amount. When printing it back out, the original zone offset has been
>> discarded and we shift by the non-DST amount because the date 1970-01-01
>> doesn't use DST. I think we'd like to be able to override the
>> getTimezoneOffset value for the Time object, but there's no easy way to
>> do that without creating our own PgTime object.

On 05/19/2010 06:43 AM, John Lister wrote:
> Hi, I spotted an issue regarding Daylight savings and timezones a while
> ago that affected UK times so it may be possible that it does the same
> in German ones. From memory - essentially the driver uses the epoch date
> to work out DST which unfortunately coincides with a year (1970) when
> the UK temporarily changed its DST rules.

This issue came up in pgsql.interfaces.jdbc and it seemed worth bringing into
the wider Java world since it's not Postgres-specific.  The problem is that
java.sql.Time values are date-agnostic so might not reflect the intended DST
offset when referencing times relevant to "now".

This might be worth a bug report, or perhaps the problem is intractable and
requires the explicit workaround that the database folks find they must use.
Perhaps the new date/time libraries will address this somehow.

Opinions or assessments?

--
Lew