Re: [HACKERS] Time bug with small years
Robert Haas writes: > On Wed, Nov 23, 2011 at 11:45 PM, Rod Taylor wrote: >> sk_test=# select '1894-01-01'::timestamp with time zone; >> timestamptz >> -- >> 1894-01-01 00:00:00-05:17:32 >> (1 row) > On my system, all current time zone offsets are multiples of 15 > minutes, but historically that wasn't the case. It seems that in your > local time zone, the offset versus UTC was, as of January 1, 1894, > minus five hours, seventeen minutes, and 32 seconds. And in fact, a quick grep through the timezone database shows that you must be using America/Toronto: # Zone NAMEGMTOFF RULES FORMAT [UNTIL] Zone America/Toronto-5:17:32 - LMT 1895 -5:00 Canada E%sT1919 -5:00 Toronto E%sT1942 Feb 9 2:00s -5:00 Canada E%sT1946 -5:00 Toronto E%sT1974 -5:00 Canada E%sT The general practice in the Olson database is to show localities as using local mean time until the year in which they adopted some form of standardized time. So local midnight of 1894-01-01 is in fact at an unusual (for us) offset from UTC. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Time bug with small years
On Wed, Nov 23, 2011 at 11:45 PM, Rod Taylor wrote: > sk_test=# select '1894-01-01'::timestamp with time zone; > timestamptz > -- > 1894-01-01 00:00:00-05:17:32 > (1 row) I believe that -05:17:32 is the offset of your local time zone as compared with UTC. For example: rhaas=# select now(); now -- 2011-11-24 13:46:46.68016+00 (1 row) rhaas=# set time zone 'Australia/Eucla'; SET rhaas=# select now(); now -- 2011-11-24 22:31:55.792565+08:45 (1 row) rhaas=# set time zone 'UTC'; SET rhaas=# select now(); now --- 2011-11-24 13:46:58.480484+00 (1 row) On my system, all current time zone offsets are multiples of 15 minutes, but historically that wasn't the case. It seems that in your local time zone, the offset versus UTC was, as of January 1, 1894, minus five hours, seventeen minutes, and 32 seconds. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Time bug with small years
On 24/11/11 04:45, Rod Taylor wrote: I have no idea what is going on with the minutes/seconds, particularly for years under 1895 where it gets appended onto the timezone component? sk_test=# select version(); version PostgreSQL 9.1.1 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit (1 row) -- uname -a output: Linux rbt-dell 3.0.0-13-generic #22-Ubuntu SMP Wed Nov 2 13:27:26 UTC 2011 x86_64 x86_64 x86_64 GNU/Linux sk_test=# select '1894-01-01'::timestamp with time zone; timestamptz -- 1894-01-01 00:00:00-05:17:32 Floating-point timestamps? Although I thought integer was the default for 9.x - hmm INSTALL says since 8.4 -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Time bug with small years
I have no idea what is going on with the minutes/seconds, particularly for years under 1895 where it gets appended onto the timezone component? sk_test=# select version(); version PostgreSQL 9.1.1 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit (1 row) -- uname -a output: Linux rbt-dell 3.0.0-13-generic #22-Ubuntu SMP Wed Nov 2 13:27:26 UTC 2011 x86_64 x86_64 x86_64 GNU/Linux sk_test=# select '1894-01-01'::timestamp with time zone; timestamptz -- 1894-01-01 00:00:00-05:17:32 (1 row) sk_test=# select '1895-01-01'::timestamp with time zone; timestamptz 1895-01-01 00:17:32-05 (1 row) sk_test=# select '1896-01-01'::timestamp with time zone; timestamptz 1896-01-01 00:00:00-05 (1 row) sk_test=# show timezone; TimeZone --- localtime (1 row) sk_test=# set timezone= 'est5edt'; SET sk_test=# select '1895-01-01'::timestamp with time zone; timestamptz 1895-01-01 00:00:00-05 (1 row) sk_test=# select '1894-01-01'::timestamp with time zone; timestamptz 1894-01-01 00:00:00-05 (1 row) I can duplicate with the exact same version of Pg on Intel hardware with kernel: Linux infongd2888 2.6.28.8-20101130b-iscsi-ntacker-fasync-mremap-amd-sec6-grsec #1 SMP Tue Nov 30 18:27:29 CET 2010 i686 GNU/Linux
