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