Обсуждение: Issue when displaying TIMESTAMPTZ values

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

Kasper Rönning <kasper.ronning@reliabit.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 <kasper.ronning@reliabit.fi> writes:
> 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.

No, the query result is the same, it's just more fully specified.
        regards, tom lane

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:
> Kasper Rönning<kasper.ronning@reliabit.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.ronning@reliabit.fi
Tel: +358-445 010 634