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