Обсуждение: Problems storing timestamps with calendars

Hi,

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?


Regards,
Martin

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

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

1. Converting the timestamp into the calendar's timezone when updating the database (as assumed by Oracle, MSSQL, DB2 etc.), or
2. Assuming that the timestamp was created using the wrong timezone, so reversing the timezone calculation (as assumed by postgres).

• Inserts a row using setTimestamp(int, Timestamp), then retrieves the row using getTimestamp(int), followed by getTimestamp(int, GMT calendar)
• Inserts a row using setTimestamp(int, Timestamp, GMT calendar), then retrieves it using the 2 methods above.

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

John Dickson wrote:

> We've found the same problem with the postgres driver - the versions of
> setTimestamp() and getTimestamp() taking Calendars adjust the timezone
> in the wrong direction.

I have a substantial rewrite of get/setTimestamp() waiting in the wings
that I'm planning to commit shortly. It fixes several problems with the
Calendar-using variants, and also should fix assignment to timestamp
(not timestamptz) columns. Can you send me your testcase so I can check
that it works ok?

-O