Обсуждение: BUG #2768: dates before year 1600 in timestamptz column give strange results
BUG #2768: dates before year 1600 in timestamptz column give strange results
От
"Mikko Tiihonen"
Дата:
The following bug has been logged online: Bug reference: 2768 Logged by: Mikko Tiihonen Email address: mikko.tiihonen@iki.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.
"Mikko Tiihonen" <mikko.tiihonen@iki.fi> writes: > 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) [ 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
Re: BUG #2768: dates before year 1600 in timestamptz column give strange results
От
"Heikki Linnakangas"
Дата:
Heikki Linnakangas wrote: > Tom Lane wrote: >> The weird offset from GMT is probably a function of your local >> timezone, which you didn't >> mention. > > Most likely "Europe/Helsinki". I can reproduce the problem with that: Confirmed that it's expected behavior. The timezone really was GMT+01:39:52 until May 1st, 1921. See http://igs.kirjastot.fi/iGS/kysymykset/haku.aspx?word=Kes%C3%A4aika and search for "1921" for details (in Finnish). -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Re: BUG #2768: dates before year 1600 in timestamptz column give strange results
От
"Heikki Linnakangas"
Дата:
Tom Lane wrote: > "Mikko Tiihonen" <mikko.tiihonen@iki.fi> writes: >> 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) > > [ shrug... ] Floating-point timestamps are not perfectly accurate; the > further away you go from 2000-01-01, the less so. Agreed. > The weird offset from GMT is probably a function of your local timezone, which you didn't > mention. 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
Mikko Tiihonen <mikko.tiihonen@iki.fi> writes: > On Mon, 20 Nov 2006, Tom Lane wrote: >> ... The weird offset from GMT is probably a function of your local >> timezone, which you didn't mention. > My database in configured to timezone Europe/Helsinki aka +0200. 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
On Mon, 20 Nov 2006, Tom Lane wrote: > "Mikko Tiihonen" <mikko.tiihonen@iki.fi> writes: > > 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) > > [ 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. My database in configured to timezone Europe/Helsinki aka +0200. I knew that the floating point stamps are not fully accurate, but I showed the result with that setting too, just to prove that the internal storing/handling format did not matter in this bug. Where can I find out what function is used for my timezone ? And even if there would be different timezone calculation formulas for different timezones I do not believe that the date of the timestamp should be a parameter that affects the result. Further notice that the insert statements included a fully qualified timestamp including the timezone so that the database local timezone won't affect the inserted value. On the other hand I do think that the values printed by select can be affected by the database timezone. I would very much like someone to try the small insert/select statements in my bug report and to verify if the bug is in my setup or if it is a real bug. My current quess is that when doing the math of converting from gregorian calendar system to julian (or vise versa) postgresql forgets to clear the time component from the timestamp before adjusting the date, resulting in the time shifted with some strange constant. -Mikko
On Tue, 21 Nov 2006, Heikki Linnakangas wrote: > Heikki Linnakangas wrote: > > Tom Lane wrote: > > > The weird offset from GMT is probably a function of your local timezone, > > > which you didn't > > > mention. > > > > Most likely "Europe/Helsinki". I can reproduce the problem with that: > > Confirmed that it's expected behavior. The timezone really was GMT+01:39:52 > until May 1st, 1921. See > > http://igs.kirjastot.fi/iGS/kysymykset/haku.aspx?word=Kes%C3%A4aika > > and search for "1921" for details (in Finnish). Ok. Thank you for clearing this up. I'm sorry to have bothered you with the issue. I'll go back to wondering why the java jdbc postgresql driver is 3x slower when it sees the odd timezone in the response. -Mikko