Issue when displaying TIMESTAMPTZ values - Mailing list pgsql-bugs

From Kasper Rönning
Subject Issue when displaying TIMESTAMPTZ values
Date
Msg-id 4FD0B1C4.7040307@reliabit.fi
Whole thread Raw
Responses Re: Issue when displaying TIMESTAMPTZ values
List pgsql-bugs
Hi,

I seem to have found a bug in Postgres 9.1.3. Apparently timestamp
values are stored correctly in the database, but querying it returns
invalid results. My environment is Windows 7 64bit. The unexpected
result is that timestamps before 1st of May 1921 are displayed
incorrectly when time zone is 'Europe/Helsinki'.

----------------- [snip] ----------------------

SELECT version();

DROP TABLE IF EXISTS test2;
CREATE TABLE test2 (ts TIMESTAMPTZ NOT NULL DEFAULT NOW());

INSERT INTO test2 VALUES ('1921-01-01 00:00:00+00');
INSERT INTO test2 VALUES ('1921-04-29 00:00:00+00');
INSERT INTO test2 VALUES ('1921-04-30 00:00:00+00');
INSERT INTO test2 VALUES ('1921-05-01 00:00:00+00');
INSERT INTO test2 VALUES ('1922-01-01 00:00:00+00');
INSERT INTO test2 VALUES ('1999-01-08 04:05:06 -8:00');
INSERT INTO test2 VALUES ('today allballs');

SET TIME ZONE EET;
SELECT * FROM test2;
--            ts
-- ------------------------
--  1921-01-01 02:00:00+02
--  1921-04-29 02:00:00+02
--  1921-04-30 02:00:00+02
--  1921-05-01 02:00:00+02
--  1922-01-01 02:00:00+02
--  1999-01-08 14:05:06+02
--  2012-06-07 03:00:00+03
-- (7 rows)

SET TIME ZONE 'Europe/Helsinki';
SELECT * FROM test2;
--               ts
-- ------------------------------
--  1921-01-01 01:39:52+01:39:52
--  1921-04-29 01:39:52+01:39:52
--  1921-04-30 01:39:52+01:39:52
--  1921-05-01 02:00:00+02
--  1922-01-01 02:00:00+02
--  1999-01-08 14:05:06+02
--  2012-06-07 03:00:00+03
-- (7 rows)

----------------- [snip] ----------------------

Best regards
Kasper Rönning



pgsql-bugs by date:

Previous
From: Andres Freund
Date:
Subject: Re: BUG #6661: out-of-order XID insertion in KnownAssignedXids
Next
From: Tom Lane
Date:
Subject: Re: Issue when displaying TIMESTAMPTZ values