Re: Problems storing timestamps with calendars - Mailing list pgsql-jdbc

From Martin Keller
Subject Re: Problems storing timestamps with calendars
Date
Msg-id 42760D89.8080404@unitedplanet.de
Whole thread Raw
In response to Re: Problems storing timestamps with calendars  (Kris Jurka <books@ejurka.com>)
List pgsql-jdbc
Hello Kris,

unfortunately, this fix doesn't solve the problem.
As far as I understand, this topic isn't that much complicated:

- Date#getTime() returns the number of milliseconds since January 1,
1970, 00:00:00 GMT.
  It's not affected by the timezone of the date object. So there's no
need to consider the (deprecated)
  timezone offset.
- Timezone#getOffset() takes care of the daylight savings. It's not a
good idea to do this by your self
  since (so far as I know) there are regions on the earth with daylight
savings which defer from one hour.

I checked the behaviour of setTimeStamp against some other databases (
Oracle, DB2,  MS SQL with jTDS) and they did all the same.
Maybe the following comparison can help you. All Servers and the java vm
are running in the same timezone (Europe/Berlin, summertime).

Input                                                DB Content

PostgreSQL           Oracle/DB2/MS SQL
1.1.1970 00:00:00 UTC, no calendar specified         1970-01-01
01:00:00  1970-01-01 01:00:00
1.1.1970 00:00:00 UTC with UTC calendar              1970-01-01
02:00:00  1970-01-01 00:00:00
1.1.1970 00:00:00 UTC with CET calendar              1970-01-01
01:00:00  1970-01-01 01:00:00
1.1.1970 00:00:00 UTC mit America/Montreal-calendar  1970-01-01
07:00:00  1969-12-31 19:00:00


Regards,
Martin



Kris Jurka wrote:

>On Wed, 27 Apr 2005, Martin Keller wrote:
>
>
>
>>We have some trouble with storing timestamps via
>>PreparedStatement.setTimestamp(int, Timestamp, Calendar) into a
>>timestamp column without timezone.
>>It looks like that the timestamp values are adjusted in the wrong
>>direction if the timezone of the calendar is not the default timezone of
>>the java vm.
>>
>>Are we doing anything wrong or is this a bug?
>>
>>
>>
>
>Something is definitely wrong here.  At first glance it doesn't look
>like it's related to the vm timezone, but instead
>AbstractJdbc2Statement.changeTime should be respecting Add for the DST
>offset, but I'm not quite sure of that.  It gets complicated when you've
>got to consider four different possible timezones (server, vm, timestamp,
>calendar).  Does this jar file fix things for you?
>
>http://www.ejurka.com/pgsql/jars/mk/
>
>Kris Jurka
>
>


pgsql-jdbc by date:

Previous
From: "Nico"
Date:
Subject: Re: executeQuery returns postgresql.stat.result
Next
From: "Nico"
Date:
Subject: mysql/posgresql pooling