Skip site navigation (1) Skip section navigation (2)

timestamp moves when setting and getting a value from postgresql

From: Martin Taal <mtaal(at)elver(dot)org>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: timestamp moves when setting and getting a value from postgresql
Date: 2005-09-02 08:44:45
Message-ID: 431810FD.8090604@elver.org (view raw or flat)
Thread:
Lists: pgsql-jdbc
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(at)elver(dot)org
web: www.elver.org

Responses

pgsql-jdbc by date

Next:From: Martin TaalDate: 2005-09-02 09:29:24
Subject: Re: timestamp moves when setting and getting a value from
Previous:From: Guido NeitzerDate: 2005-09-02 05:49:11
Subject: Re: Prepared statement not using an index

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group