The following bug has been logged online:

Bug reference: 2768
Logged by: Mikko Tiihonen
Email address: mikko.tiihonen (AT) iki (DOT) fi
PostgreSQL version: 8.1.5
Operating system: Linux/amd64/gcc-4.1.1
Description: dates before year 1600 in timestamptz column give
strange results
Details:

createuser -s -d test
createdb -E LATIN9 -O test test
psql -U test
---
CREATE TEMP TABLE tester (stamp timestamp(6) with time zone);
INSERT INTO tester (stamp) VALUES ( '0134-05-06 09:12:34.123456 +0200' );
INSERT INTO tester (stamp) VALUES ( '2134-05-06 09:12:34.123456 +0200' );
SELECT * FROM tester;
---
PostgreSQL compiled with intdatetime=on
stamp
----------------------------------
0134-05-06 08:52:26.123456+01:39
2134-05-06 09:12:34.123456+02
(2 rows)

PostgreSQL compiled with intdatetime=off
stamp
----------------------------------
0134-05-06 08:52:26.123459+01:39
2134-05-06 09:12:34.123456+02
(2 rows)


If I fetch the rows using v3 protocol with and binary encoding for the
timestamp field I get back has the correct time. If I fetch the rows using
text encoding I get the same erronous value as with psql.

That makes me think the problem is in converting the internal timestamptz
format to formatted text.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

"Mikko Tiihonen" <mikko.tiihonen (AT) iki (DOT) fi> writes:
[ shrug... ] Floating-point timestamps are not perfectly accurate; the
further away you go from 2000-01-01, the less so. The weird offset from
GMT is probably a function of your local timezone, which you didn't
mention.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Heikki Linnakangas wrote:
Confirmed that it's expected behavior. The timezone really was
GMT+01:39:52 until May 1st, 1921. See

http://igs.kirjastot.fi/iGS/kysymyks...=Kes%C3%A4aika

and search for "1921" for details (in Finnish).

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane wrote:
Agreed.

Most likely "Europe/Helsinki". I can reproduce the problem with that:

postgres=# set time zone 'Europe/Helsinki';
SET
postgres=# SELECT * FROM tester;
stamp
-------------------------------------
0134-05-06 08:52:26.123459+01:39:52
2134-05-06 09:12:34.123456+02
(2 rows)

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Mikko Tiihonen <mikko.tiihonen (AT) iki (DOT) fi> writes:
The zic database says that Helsinki kept local mean solar time before
1921:

# 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 1981 Mar 29 2:00
2:00 EU EE%sT

Of course, back in the 1600's they probably didn't keep time as
accurately as all that, but feel free to change your copy of that
configuration file if you want a different answer. I'd be willing
to bet that around 1900, the 1:39 offset was indeed correct.

FWIW, pre-8.2 Postgres does have some issues with displaying
fractional-minute GMT offsets. PG 8.1.5:

regression=# set timezone = 'Europe/Helsinki';
SET
regression=# select '1600-01-01'::timestamptz;
timestamptz
---------------------------
1600-01-01 00:00:00+01:39
(1 row)

CVS HEAD gets it right:

regression=# set timezone = 'Europe/Helsinki';
SET
regression=# select '1600-01-01'::timestamptz;
timestamptz
------------------------------
1600-01-01 00:00:00+01:39:52
(1 row)


regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly