Issue when displaying TIMESTAMPTZ values

From: Kasper Rönning <kasper(dot)ronning(at)reliabit(dot)fi>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Issue when displaying TIMESTAMPTZ values
Date: 2012-06-07 13:51:00
Message-ID: 4FD0B1C4.7040307@reliabit.fi
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andres Freund 2012-06-07 13:58:24 Re: BUG #6661: out-of-order XID insertion in KnownAssignedXids
Previous Message Valentine Gogichashvili 2012-06-07 11:07:19 Re: BUG #6375: tsearch does not recognize all valid emails