Обсуждение: Julian Day 0 question
Ran across something that is confusing me. The docs for to_char indicates that julian day 0 is January 1, 4712 BC at midnight. http://www.postgresql.org/docs/8.3/static/functions-formatting.html When I run to_char, I don't get 0 for that date. postgres=# select to_char('4712-01-01 BC'::date, 'J'); to_char --------- 404 I get julian day 0 for 4714-11-24 BC. postgres=# select to_char('4714-11-24 BC'::date, 'J'); to_char --------- 0 Output of 'select version()' PostgreSQL 8.3devel on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 20070105 (Red Hat 4.1.1-52) andrew
On 14/12/2007, Andrew Chernow <ac@esilo.com> wrote: > Ran across something that is confusing me. The docs for to_char > indicates that julian day 0 is January 1, 4712 BC at midnight. > > http://www.postgresql.org/docs/8.3/static/functions-formatting.html > > When I run to_char, I don't get 0 for that date. > > postgres=# select to_char('4712-01-01 BC'::date, 'J'); > to_char > --------- > 404 > > I get julian day 0 for 4714-11-24 BC. > > postgres=# select to_char('4714-11-24 BC'::date, 'J'); > to_char > --------- > 0 > > Output of 'select version()' > > PostgreSQL 8.3devel on x86_64-unknown-linux-gnu, compiled by GCC gcc > (GCC) 4.1.1 20070105 (Red Hat 4.1.1-52) > > andrew > there is more strange things postgres=# select to_date('0', 'J'); to_date --------------- 0001-01-01 BC (1 row) it's wrong, correct is probably ERROR: timestamp out of range postgres=# select to_date('1', 'J'); to_date --------------- 4714-11-25 BC (1 row) Regards Pavel Stehule
Pavel Stehule wrote: > On 14/12/2007, Andrew Chernow <ac@esilo.com> wrote: >> Ran across something that is confusing me. The docs for to_char >> indicates that julian day 0 is January 1, 4712 BC at midnight. >> >> http://www.postgresql.org/docs/8.3/static/functions-formatting.html >> >> When I run to_char, I don't get 0 for that date. >> >> postgres=# select to_char('4712-01-01 BC'::date, 'J'); >> to_char >> --------- >> 404 >> >> I get julian day 0 for 4714-11-24 BC. >> >> postgres=# select to_char('4714-11-24 BC'::date, 'J'); >> to_char >> --------- >> 0 >> >> Output of 'select version()' >> >> PostgreSQL 8.3devel on x86_64-unknown-linux-gnu, compiled by GCC gcc >> (GCC) 4.1.1 20070105 (Red Hat 4.1.1-52) >> >> andrew >> > > there is more strange things > > postgres=# select to_date('0', 'J'); > to_date > --------------- > 0001-01-01 BC > (1 row) > > it's wrong, correct is probably ERROR: timestamp out of range > > postgres=# select to_date('1', 'J'); > to_date > --------------- > 4714-11-25 BC > (1 row) > > Regards > Pavel Stehule > > Looks like a difference in calendars: I think the docs give the starting date in Julian proleptic Calendar while to_char returns Gregorian proleptic Calendar. andrew
Andrew Chernow <ac@esilo.com> writes: > Looks like a difference in calendars: I think the docs give the starting > date in Julian proleptic Calendar while to_char returns Gregorian > proleptic Calendar. Yeah. We're definitely using Gregorian counting, because we're omitting leap years at multiples of 400 years: regression=# select 'J114417'::date; date --------------- 4400-02-28 BC (1 row) regression=# select 'J114418'::date; date --------------- 4400-03-01 BC (1 row) So as far as that goes, I'm inclined to leave the code alone and adjust the docs. The regular date input routine hasn't got a problem with J0: regression=# select 'J1'::date; date --------------- 4714-11-25 BC (1 row) regression=# select 'J0'::date; date --------------- 4714-11-24 BC (1 row) so to_date's behavior in the case seems a bit broken, but I'm not sure if it's worth fixing. IIRC that code uses zero to mean "field not entered", so it'd need some ugly hack to handle this. regards, tom lane