Re: [HACKERS] Dates BC.
I have applied a patch to fix the issues mentioned below. Thanks.
---
Karel Zak wrote:
On Fri, Dec 19, 2003 at 01:12:08AM -0800, Dann Corbit wrote:
There is no zero calendar year. The first year of Anno Domini is 1. It's
ordinal, not cardinal.
I agree. But the follow quoted code is not use in date_part() there
Kurt found bug. It's used in to_timestamp() _only_, and it works,
because tm2timestamp() and date2j() work with zero year.
Is there connection between formatting.c and date_part() ?
I don't think so...
In backend/utils/adt/formatting.c:
if (tmfc.bc)
{
if (tm-tm_year 0)
tm-tm_year = -(tm-tm_year - 1);
... tm-tm_year = -(tm-tm_year - 1) is used for:
# select to_timestamp('0001/01/01 BC', '/MM/DD AD');
to_timestamp
0001-01-01 00:00:00 BC
and it's OK.
I think a bug is somewhere in timestamp2tm() which used in next
examples and it's shared between more functions:
# select to_char('0001-01-01 BC'::date, '/MM/DD AD');
to_char
---
/01/01 AD
# SELECT EXTRACT(YEAR from '0001-01-01 BC'::date);
date_part
---
0
Karel
--
Karel Zak [EMAIL PROTECTED]
http://home.zf.jcu.cz/~zakkr/
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] Dates BC.
On Thu, Dec 18, 2003 at 02:11:20PM +0100, Kurt Roeckx wrote:
I find this a little strange:
select date_part('year', '0002-01-01 BC'::date);
date_part
---
-1
It seems 1 BC and 0 are the same year.
Is there connection between formatting.c and date_part() ?
I don't think so...
In backend/utils/adt/formatting.c:
if (tmfc.bc)
{
if (tm-tm_year 0)
tm-tm_year = -(tm-tm_year - 1);
It this normal or a bug?
I think this code is OK, butg is somethere in extract (date_part) code.
test=# select to_date('0020-01-10 BC'::text, '-MM-DD BC');
to_date
---
0020-01-10 BC
(1 dka)
test=# select to_date('0020-01-10 AD'::text, '-MM-DD BC');
to_date
0020-01-10
test=# select to_char('0020-01-10 BC'::date, '-MM-DD AD');
to_char
---
0020-01-10 BC
Karel
--
Karel Zak [EMAIL PROTECTED]
http://home.zf.jcu.cz/~zakkr/
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Dates BC.
There is no zero calendar year. The first year of Anno Domini is 1. It's ordinal,
not cardinal.
-Original Message-
From: Karel Zak [mailto:[EMAIL PROTECTED]
Sent: Friday, December 19, 2003 12:04 AM
To: Kurt Roeckx
Cc: [EMAIL PROTECTED]
Subject: Re: [HACKERS] Dates BC.
On Thu, Dec 18, 2003 at 02:11:20PM +0100, Kurt Roeckx wrote:
I find this a little strange:
select date_part('year', '0002-01-01 BC'::date);
date_part
---
-1
It seems 1 BC and 0 are the same year.
Is there connection between formatting.c and date_part() ?
I don't think so...
In backend/utils/adt/formatting.c:
if (tmfc.bc)
{
if (tm-tm_year 0)
tm-tm_year = -(tm-tm_year - 1);
It this normal or a bug?
I think this code is OK, butg is somethere in extract
(date_part) code.
test=# select to_date('0020-01-10 BC'::text, '-MM-DD BC');
to_date
---
0020-01-10 BC
(1 dka)
test=# select to_date('0020-01-10 AD'::text, '-MM-DD BC');
to_date
0020-01-10
test=# select to_char('0020-01-10 BC'::date, '-MM-DD AD');
to_char
---
0020-01-10 BC
Karel
--
Karel Zak [EMAIL PROTECTED]
http://home.zf.jcu.cz/~zakkr/
---(end of
broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] Dates BC.
On Fri, Dec 19, 2003 at 01:12:08AM -0800, Dann Corbit wrote:
There is no zero calendar year. The first year of Anno Domini is 1. It's ordinal,
not cardinal.
I agree. But the follow quoted code is not use in date_part() there
Kurt found bug. It's used in to_timestamp() _only_, and it works,
because tm2timestamp() and date2j() work with zero year.
Is there connection between formatting.c and date_part() ?
I don't think so...
In backend/utils/adt/formatting.c:
if (tmfc.bc)
{
if (tm-tm_year 0)
tm-tm_year = -(tm-tm_year - 1);
... tm-tm_year = -(tm-tm_year - 1) is used for:
# select to_timestamp('0001/01/01 BC', '/MM/DD AD');
to_timestamp
0001-01-01 00:00:00 BC
and it's OK.
I think a bug is somewhere in timestamp2tm() which used in next
examples and it's shared between more functions:
# select to_char('0001-01-01 BC'::date, '/MM/DD AD');
to_char
---
/01/01 AD
# SELECT EXTRACT(YEAR from '0001-01-01 BC'::date);
date_part
---
0
Karel
--
Karel Zak [EMAIL PROTECTED]
http://home.zf.jcu.cz/~zakkr/
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Dates BC.
Kurt Roeckx wrote:
I find this a little strange:
select date_part('year', '0002-01-01 BC'::date);
date_part
---
-1
It seems 1 BC and 0 are the same year.
In backend/utils/adt/formatting.c:
if (tmfc.bc)
{
if (tm-tm_year 0)
tm-tm_year = -(tm-tm_year - 1);
It this normal or a bug?
Uh, well, yea, there was no year 0. However, it seems we should return
the proper year. My guess is that missing year 0 is the cause, and
there are certain reasons year 2 BC should return -1. If you are
subtracting dates, like 32AD - 4BC, you get 35, which is the proper
number of years spanned.
I am not sure what is the proper answer. I thought date_part just
grabbed parts of the date like it says, but obviously not, and there
are some good reasons for it, I guess.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] Dates BC.
In article [EMAIL PROTECTED] you wrote:
I find this a little strange:
select date_part('year', '0002-01-01 BC'::date);
date_part
---
-1
It seems 1 BC and 0 are the same year.
There is an unresolveable legacy problem here, in that Brahmagupta did
not yet invent the mathematical concept of 0 until ~ 598 CE, by which
time the Roman Empire had fallen (depending on whether you believe it
actually fell). We'll just have to live with some weirdness on this
one. :)
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100cell: +1 415 235 3778
This is my .sig. There are many like it, but this one is mine.
---(end of broadcast)---
TIP 8: explain analyze is your friend
