Hi,
I have a problem when saving and retrieving a timestamp from postgresql.
When I save a timestamp and retrieve it it gets moved two hours (my
timezoneoffset from gtm).

For example when I store 2 Sept. 2005 10:12:12 + 2. I get 2 Sept.
12:12:12+2 back.
When I look in postgresql (psql and then commandline select * from
table) then I can see that it stores 2 Sept. 12:12:12+2 in the timestamp
field although the time I pass in is 2 Sept. 2005 10:12:12 + 2.

Environment:
linux
postgresql 8.0.2
latest jdbc driver: 8.0-312 jdbc 2

The field type in the database is timestamptz

I debugged through the method setTimeStamp method and getTimeStamp
methods in
AbstractJDBC2Statement. My input to setTimeStamp:
a timestamp 2 Sept. 10:12:12 CEST
a calendar with timezone UTC

This results in the database in the timestamptz field in a value: 2
Sept. 2005 12:12:12 + 2.
In gettimestamp this same value is returned.

It seems that the changeTime method in AbstractJDBC2Statement actually
adds two hours (should it not subtract 2 hours to get from CEST to GMT?)
in addition the time zone of the computed value is set to CEST while I
pass a UTC calendar (so the timestamp field in the database should
actually contain: 2 Sept. 2005 8:12:12 + 0.

As an extra info the setTimeStamp(int, timestamp, calendar) method calls
setTimeStamp(int, timestamp) method. In this
last method a new GregorianCalendar is created. The timezone of this new
calendar is Europe/Amsterdam (my system timezone apparently).

I tried different things like instead of passing in a calendar with UTC
timezone, I tried Europe/Amsterdam. But this gave the same result.
The timezone setting of postgresql is Europe/Amsterdam.

Did I miss something or am I doing something wrong?
I am sorry if I missed something obvious.

--

With Regards, Martin Taal

The Elver Project
Barchman Wuytierslaan 72b
3818 LK Amersfoort
tel: +31 (0)33 462 02 07
fax: +31 (0)33 463 77 12
Mobile: +31 (0)6 288 48 943
email: mtaal@elver.org
web: www.elver.org

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Hi Martin,

this issue was discussed in several threads a few weeks ago.
There is a patch by Oliver Jowett commited to the head branch of the
project.
It's not in the current 312 release.

****
Martin


Martin Taal wrote:

> Hi,
> I have a problem when saving and retrieving a timestamp from postgresql.
> When I save a timestamp and retrieve it it gets moved two hours (my
> timezoneoffset from gtm).
>
> For example when I store 2 Sept. 2005 10:12:12 + 2. I get 2 Sept.
> 12:12:12+2 back.
> When I look in postgresql (psql and then commandline select * from
> table) then I can see that it stores 2 Sept. 12:12:12+2 in the
> timestamp field although the time I pass in is 2 Sept. 2005 10:12:12 + 2.
>
> Environment:
> linux
> postgresql 8.0.2
> latest jdbc driver: 8.0-312 jdbc 2
>
> The field type in the database is timestamptz
>
> I debugged through the method setTimeStamp method and getTimeStamp
> methods in
> AbstractJDBC2Statement. My input to setTimeStamp:
> a timestamp 2 Sept. 10:12:12 CEST
> a calendar with timezone UTC
>
> This results in the database in the timestamptz field in a value: 2
> Sept. 2005 12:12:12 + 2.
> In gettimestamp this same value is returned.
>
> It seems that the changeTime method in AbstractJDBC2Statement actually
> adds two hours (should it not subtract 2 hours to get from CEST to
> GMT?) in addition the time zone of the computed value is set to CEST
> while I pass a UTC calendar (so the timestamp field in the database
> should actually contain: 2 Sept. 2005 8:12:12 + 0.
>
> As an extra info the setTimeStamp(int, timestamp, calendar) method
> calls setTimeStamp(int, timestamp) method. In this
> last method a new GregorianCalendar is created. The timezone of this
> new calendar is Europe/Amsterdam (my system timezone apparently).
>
> I tried different things like instead of passing in a calendar with
> UTC timezone, I tried Europe/Amsterdam. But this gave the same result.
> The timezone setting of postgresql is Europe/Amsterdam.
>
> Did I miss something or am I doing something wrong?
> I am sorry if I missed something obvious.
>


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Hi Martin,
Thanks for the quick reply.
I have searched the threads and looked in cvs but I missed this..., sorry.

When will the next release of the jdbc driver be made available?

gr. Martin

Martin Keller wrote:
> Hi Martin,
>
> this issue was discussed in several threads a few weeks ago.
> There is a patch by Oliver Jowett commited to the head branch of the
> project.
> It's not in the current 312 release.
>
> ****
> Martin
>
>
> Martin Taal wrote:
>
>> Hi,
>> I have a problem when saving and retrieving a timestamp from postgresql.
>> When I save a timestamp and retrieve it it gets moved two hours (my
>> timezoneoffset from gtm).
>>
>> For example when I store 2 Sept. 2005 10:12:12 + 2. I get 2 Sept.
>> 12:12:12+2 back.
>> When I look in postgresql (psql and then commandline select * from
>> table) then I can see that it stores 2 Sept. 12:12:12+2 in the
>> timestamp field although the time I pass in is 2 Sept. 2005 10:12:12 + 2.
>>
>> Environment:
>> linux
>> postgresql 8.0.2
>> latest jdbc driver: 8.0-312 jdbc 2
>>
>> The field type in the database is timestamptz
>>
>> I debugged through the method setTimeStamp method and getTimeStamp
>> methods in
>> AbstractJDBC2Statement. My input to setTimeStamp:
>> a timestamp 2 Sept. 10:12:12 CEST
>> a calendar with timezone UTC
>>
>> This results in the database in the timestamptz field in a value: 2
>> Sept. 2005 12:12:12 + 2.
>> In gettimestamp this same value is returned.
>>
>> It seems that the changeTime method in AbstractJDBC2Statement actually
>> adds two hours (should it not subtract 2 hours to get from CEST to
>> GMT?) in addition the time zone of the computed value is set to CEST
>> while I pass a UTC calendar (so the timestamp field in the database
>> should actually contain: 2 Sept. 2005 8:12:12 + 0.
>>
>> As an extra info the setTimeStamp(int, timestamp, calendar) method
>> calls setTimeStamp(int, timestamp) method. In this
>> last method a new GregorianCalendar is created. The timezone of this
>> new calendar is Europe/Amsterdam (my system timezone apparently).
>>
>> I tried different things like instead of passing in a calendar with
>> UTC timezone, I tried Europe/Amsterdam. But this gave the same result.
>> The timezone setting of postgresql is Europe/Amsterdam.
>>
>> Did I miss something or am I doing something wrong?
>> I am sorry if I missed something obvious.
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>

--

With Regards, Martin Taal

Springsite
Barchman Wuytierslaan 72b
3818 LK Amersfoort
tel: +31 (0)33 462 02 07
fax: +31 (0)33 463 77 12
Mobile: +31 (0)6 288 48 943
email: mtaal@springsite.com
web: www.springsite.com

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings