Thread: Calendar vs. Timestamp
I move this issue from the general mailing list. Here is my problem: In the Java application, I create a Calendar object and the getTime and getTimeZone return the following values: * Sun Apr 09 12:40:52 PDT 2006 * Pacific Standard Time The Calendar object is matched to the timestamp with time zone data type throught Hibernate. And the above data is storedas the following: 2006-04-09 14:40:53.093-07 That is not correct. The command of "select now()" returns a correct value. The PG version is 8.0.x and the jdbc versionis 8.0.309.jdbc3. Is it a bug in the driver? Thanks. -- _______________________________________________ Search for businesses by name, location, or phone number. -Lycos Yellow Pages http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.asp?SRC=lycos10
On Tue, 11 Apr 2006, Wei Wei wrote: > I move this issue from the general mailing list. Here is my problem: > > In the Java application, I create a Calendar object and the getTime and > getTimeZone return the following values: > > * Sun Apr 09 12:40:52 PDT 2006 > * Pacific Standard Time > > The Calendar object is matched to the timestamp with time zone data type > throught Hibernate. And the above data is stored as the following: > > 2006-04-09 14:40:53.093-07 > > That is not correct. The command of "select now()" returns a correct > value. The PG version is 8.0.x and the jdbc version is 8.0.309.jdbc3. Is > it a bug in the driver? > The 8.1 driver has better timezone/calendar support. Please try the latest 8.1 release and if that doesn't fix your problem, send us the java code to reproduce your problem. Kris Jurka
> > Is 8.1 driver compatible with PG 8.0? I can easily update the PG > > version on my development box. But, it wouldn't easy for the > > deployment box. > > > > Yes, the 8.1 driver is backward compatible with all server releases > back to 7.2. As always it's good to test on your development box > first, but I don't see any problems with an upgrade. > After having a 8.1 driver, the timestamp is stored correctly in the DB. So, it indeed is a bug in the 8.0 driver. I would like to clarify one observation. The timestamp seems only take the date section data of a calendar object regardlessthe value of the time zone. If so, a calendar object with a specified time zone would not make any difference incomparsion to a calendar object with the default time zone. Or, the default time zone is always used in PG. Thanks. -- _______________________________________________ Search for businesses by name, location, or phone number. -Lycos Yellow Pages http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.asp?SRC=lycos10
On Tue, 11 Apr 2006, Wei Wei wrote:
> I would like to clarify one observation. The timestamp seems only take
> the date section data of a calendar object regardless the value of the
> time zone. If so, a calendar object with a specified time zone would not
> make any difference in comparsion to a calendar object with the default
> time zone. Or, the default time zone is always used in PG.
>
I think the confusion arises from the fact that the server always stores
timestamps in utc and always returns them based upon the current setting
of the TimeZone configuration parameter.
CREATE TABLE tstest(a timestamptz);
INSERT INTO tstest VALUES ('2006-04-11 16:15:28-07');
INSERT INTO tstest VALUES ('2006-04-11 16:15:28-08');
SET TimeZone = 'America/Los_Angeles';
INSERT INTO tstest VALUES ('2006-04-11 16:15:28');
SELECT * FROM tstest;
SET TimeZone = 'America/New_York';
INSERT INTO tstest VALUES ('2006-04-11 16:15:28');
SELECT * FROM tstest;
Kris Jurka
> > I would like to clarify one observation. The timestamp seems only
> > take the date section data of a calendar object regardless the
> > value of the time zone. If so, a calendar object with a specified
> > time zone would not make any difference in comparsion to a
> > calendar object with the default time zone. Or, the default time
> > zone is always used in PG.
> >
>
> I think the confusion arises from the fact that the server always
> stores timestamps in utc and always returns them based upon the
> current setting of the TimeZone configuration parameter.
>
> CREATE TABLE tstest(a timestamptz);
> INSERT INTO tstest VALUES ('2006-04-11 16:15:28-07');
> INSERT INTO tstest VALUES ('2006-04-11 16:15:28-08');
> SET TimeZone = 'America/Los_Angeles';
> INSERT INTO tstest VALUES ('2006-04-11 16:15:28');
> SELECT * FROM tstest;
2006-04-11 16:15:28-07
2006-04-11 17:15:28-07
2006-04-11 16:15:28-07
> SET TimeZone = 'America/New_York';
> INSERT INTO tstest VALUES ('2006-04-11 16:15:28');
> SELECT * FROM tstest;
>
2006-04-11 19:15:28-04
2006-04-11 20:15:28-04
2006-04-11 19:15:28-04
2006-04-11 16:15:28-04
>
Thanks for the test code to clarify the time zone matter. I need to change my approach to show the date & time with the
users'time zones.
--
_______________________________________________
Search for businesses by name, location, or phone number. -Lycos Yellow Pages
http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.asp?SRC=lycos10