Thread: Dates BC.
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?
Kurt
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 pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
In article <20031218131120.GA11684@ping.be> 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 david@fetter.org http://fetter.org/
phone: +1 510 893 6100 cell: +1 415 235 3778
This is my .sig. There are many like it, but this one is mine.
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, 'YYYY-MM-DD BC'); to_date
---------------0020-01-10 BC
(1 řádka)
test=# select to_date('0020-01-10 AD'::text, 'YYYY-MM-DD BC'); to_date
------------0020-01-10
test=# select to_char('0020-01-10 BC'::date, 'YYYY-MM-DD AD'); to_char
---------------0020-01-10 BC Karel
-- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/
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:zakkr@zf.jcu.cz]
> Sent: Friday, December 19, 2003 12:04 AM
> To: Kurt Roeckx
> Cc: pgsql-hackers@postgresql.org
> 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, 'YYYY-MM-DD BC');
> to_date
> ---------------
> 0020-01-10 BC
> (1 řádka)
>
> test=# select to_date('0020-01-10 AD'::text, 'YYYY-MM-DD BC');
> to_date
> ------------
> 0020-01-10
>
> test=# select to_char('0020-01-10 BC'::date, 'YYYY-MM-DD AD');
> to_char
> ---------------
> 0020-01-10 BC
>
> Karel
> --
> Karel Zak <zakkr@zf.jcu.cz>
> 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
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() thereKurt 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', 'YYYY/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 nextexamples and it's shared between more
functions:
# select to_char('0001-01-01 BC'::date, 'YYYY/MM/DD AD'); to_char ---------------0000/01/01 AD
# SELECT EXTRACT(YEAR from '0001-01-01 BC'::date);date_part ----------- 0
Karel
-- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/
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', 'YYYY/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, 'YYYY/MM/DD AD');
> to_char
> ---------------
> 0000/01/01 AD
>
> # SELECT EXTRACT(YEAR from '0001-01-01 BC'::date);
> date_part
> -----------
> 0
>
>
> Karel
>
>
>
> --
> Karel Zak <zakkr@zf.jcu.cz>
> http://home.zf.jcu.cz/~zakkr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073