Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Dates BC.

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
Cc: Dann Corbit <DCorbit(at)connx(dot)com>, Kurt Roeckx <Q(at)ping(dot)be>,PostgreSQL-patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: [HACKERS] Dates BC.
Date: 2004-03-29 18:23:32
Message-ID: 200403291823.i2TINW817229@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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

Very interesting.  I am attaching a patch that fixes these cases. 
There were two bugs in our code:

	o date_part didn't handle BC years properly (must -1 year)
	o formatting code tested for BC dates as only < 0, not <= 0

Look at this before and after test output.  The attached patdch fixes
this.  Regression tests pass.

---------------------------------------------------------------------------

BEFORE:
	
	test=> SELECT EXTRACT(YEAR from '0002-01-01 AD'::date);
	 date_part
	-----------
	         2
	(1 row)
	
	test=> SELECT EXTRACT(YEAR from '0001-01-01 AD'::date);
	 date_part
	-----------
	         1
	(1 row)
	
	test=> SELECT EXTRACT(YEAR from '0001-01-01 BC'::date);
	 date_part
	-----------
	         0  **error**
	(1 row)
	
	test=> SELECT EXTRACT(YEAR from '0002-01-01 BC'::date);
	 date_part
	-----------
	        -1  **error**
	(1 row)
	
	test=> select to_char('0002-01-01 AD'::date, 'YYYY/MM/DD AD');
	    to_char
	---------------
	 0002/01/01 AD
	(1 row)
	
	test=> select to_char('0001-01-01 AD'::date, 'YYYY/MM/DD AD');
	    to_char
	---------------
	 0001/01/01 AD
	(1 row)
	
	test=> select to_char('0001-01-01 BC'::date, 'YYYY/MM/DD AD');
	    to_char
	---------------
	 0000/01/01 AD **error in year and AD**
	(1 row)
	
	test=> select to_char('0002-01-01 BC'::date, 'YYYY/MM/DD AD');
	    to_char
	---------------
	 0002/01/01 BC
	(1 row)
	
	---------------------------------------------------------------------------

AFTER:
	
	test=> SELECT EXTRACT(YEAR from '0002-01-01 AD'::date);
	 date_part
	-----------
	         2
	(1 row)
	
	test=> SELECT EXTRACT(YEAR from '0001-01-01 AD'::date);
	 date_part
	-----------
	         1
	(1 row)
	
	test=> SELECT EXTRACT(YEAR from '0001-01-01 BC'::date);
	 date_part
	-----------
	        -1
	(1 row)
	
	test=> SELECT EXTRACT(YEAR from '0002-01-01 BC'::date);
	 date_part
	-----------
	        -2
	(1 row)
	
	test=> select to_char('0002-01-01 AD'::date, 'YYYY/MM/DD AD');
	    to_char
	---------------
	 0002/01/01 AD
	(1 row)
	
	test=> select to_char('0001-01-01 AD'::date, 'YYYY/MM/DD AD');
	    to_char
	---------------
	 0001/01/01 AD
	(1 row)
	
	test=> select to_char('0001-01-01 BC'::date, 'YYYY/MM/DD AD');
	    to_char
	---------------
	 0001/01/01 BC
	(1 row)
	
	test=> select to_char('0002-01-01 BC'::date, 'YYYY/MM/DD AD');
	    to_char
	---------------
	 0002/01/01 BC
	(1 row)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

pgsql-hackers by date

Next:From: Dave PageDate: 2004-03-29 19:12:35
Subject: Re: Increasing security in a shared environment ...
Previous:From: Marc G. FournierDate: 2004-03-29 18:21:47
Subject: Re: Increasing security in a shared environment ...

pgsql-patches by date

Next:From: Bruce MomjianDate: 2004-03-29 20:37:07
Subject: Re: [HACKERS] Dates BC.
Previous:From: Fabien COELHODate: 2004-03-29 07:33:00
Subject: [NOT] (LIKE|ILIKE) (ANY|SOME|ALL) (subquery...)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group