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


Thu, 18 Dec 2003 14:11:20 +0100
Kurt Roeckx <Q@ping.be>
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
  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, Pennsylvania 19073

Thu, 18 Dec 2003 08:45:33 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Dates BC.
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.

Thu, 18 Dec 2003 07:05:24 -0800
david@fetter.org (David Fetter)
Re: 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/

Fri, 19 Dec 2003 09:04:15 +0100
Karel Zak <zakkr@zf.jcu.cz>
Re: Dates BC.