Re: Problems storing timestamps with calendars

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