Thread: timestamp moves when setting and getting a value from postgresql
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
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. >
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