Re: Issue when displaying TIMESTAMPTZ values

Hi,

Thank you for the quick reply! I was completely unaware of the old time
zones of Helsinki! However I'm afraid that the behaviour of Postgresql
seems plain wrong to me. An example:

SET TIME ZONE 'Europe/Helsinki';
DROP TABLE IF EXISTS test1;
CREATE TABLE test1 (ts TIMESTAMPTZ);
INSERT INTO test1 VALUES ('0001-01-01 00:00:00');
SELECT * FROM test1;
-- ts
-- ------------------------------
-- 0001-01-01 00:00:00+01:39:52

Here I enter a timestamp in Helsinki time zone, and the query result is
different, even though the time zone is the same.

I will resolve this matter by using TIMESTAMP WITHOUT TIME ZONE instead,
and storing the offset manually. That way I get two advantages: 1)
queried time stamp will be equal to the inserted one 2) time offset
information is stored, allowing the time to be displayed in the same
time zone as it was entered and at the point in time that it was entered.

Best regards,
Kasper Rönning

On 7.6.2012 17:37, Tom Lane wrote:
> =?ISO-8859-1?Q?Kasper_R=F6nning?=<kasper(dot)ronning(at)reliabit(dot)fi> writes:
>> 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'.
> This is not incorrect. The Olson timezone database shows:
>
> # Zone NAME GMTOFF RULES FORMAT [UNTIL]
> Zone Europe/Helsinki 1:39:52 - LMT 1878 May 31
> 1:39:52 - HMT 1921 May # Helsinki Mean Time
> 2:00 Finland EE%sT 1983
> 2:00 EU EE%sT
>
> that is, it was only in 1921 that Helsinki adopted "standard" time
> referenced to the Greenwich meridian. Before that it would have been
> typical to set clocks by local mean solar time, which is 1:39:52 east
> of Greenwich.
>
> regards, tom lane

--
Kasper Rönning
Reliabit Ay

www.reliabit.fi
kasper(dot)ronning(at)reliabit(dot)fi
Tel: +358-445 010 634