Julian Day 0 question

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