Thread: Dates BC.

Dates BC.

From
Kurt Roeckx
Date:
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



Re: Dates BC.

From
Bruce Momjian
Date:
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
 


Re: Dates BC.

From
david@fetter.org (David Fetter)
Date:
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.


Re: Dates BC.

From
Karel Zak
Date:
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/


Re: Dates BC.

From
"Dann Corbit"
Date:
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


Re: Dates BC.

From
Karel Zak
Date:
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/


Re: Dates BC.

From
Bruce Momjian
Date:
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