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
  [EMAIL PROTECTED]               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: src/backend/utils/adt/formatting.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/formatting.c,v
retrieving revision 1.72
diff -c -c -r1.72 formatting.c
*** src/backend/utils/adt/formatting.c  7 Jan 2004 18:56:28 -0000       1.72
--- src/backend/utils/adt/formatting.c  29 Mar 2004 18:16:26 -0000
***************
*** 169,175 ****
   * AC / DC
   * ----------
   */
! #define YEAR_ABS(_y)  (_y < 0 ? -(_y -1) : _y)
  #define BC_STR_ORIG " BC"
  
  #define A_D_STR               "A.D."
--- 169,175 ----
   * AC / DC
   * ----------
   */
! #define YEAR_ABS(_y)  (_y <= 0 ? -(_y -1) : _y)
  #define BC_STR_ORIG " BC"
  
  #define A_D_STR               "A.D."
***************
*** 2119,2125 ****
                case DCH_B_C:
                        if (flag == TO_CHAR)
                        {
!                               strcpy(inout, (tm->tm_year < 0 ? B_C_STR : A_D_STR));
                                return 3;
  
                        }
--- 2119,2125 ----
                case DCH_B_C:
                        if (flag == TO_CHAR)
                        {
!                               strcpy(inout, (tm->tm_year <= 0 ? B_C_STR : A_D_STR));
                                return 3;
  
                        }
***************
*** 2134,2140 ****
                case DCH_BC:
                        if (flag == TO_CHAR)
                        {
!                               strcpy(inout, (tm->tm_year < 0 ? BC_STR : AD_STR));
                                return 1;
  
                        }
--- 2134,2140 ----
                case DCH_BC:
                        if (flag == TO_CHAR)
                        {
!                               strcpy(inout, (tm->tm_year <= 0 ? BC_STR : AD_STR));
                                return 1;
  
                        }
***************
*** 2149,2155 ****
                case DCH_b_c:
                        if (flag == TO_CHAR)
                        {
!                               strcpy(inout, (tm->tm_year < 0 ? b_c_STR : a_d_STR));
                                return 3;
  
                        }
--- 2149,2155 ----
                case DCH_b_c:
                        if (flag == TO_CHAR)
                        {
!                               strcpy(inout, (tm->tm_year <= 0 ? b_c_STR : a_d_STR));
                                return 3;
  
                        }
***************
*** 2164,2170 ****
                case DCH_bc:
                        if (flag == TO_CHAR)
                        {
!                               strcpy(inout, (tm->tm_year < 0 ? bc_STR : ad_STR));
                                return 1;
  
                        }
--- 2164,2170 ----
                case DCH_bc:
                        if (flag == TO_CHAR)
                        {
!                               strcpy(inout, (tm->tm_year <= 0 ? bc_STR : ad_STR));
                                return 1;
  
                        }
Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.102
diff -c -c -r1.102 timestamp.c
*** src/backend/utils/adt/timestamp.c   22 Mar 2004 01:38:17 -0000      1.102
--- src/backend/utils/adt/timestamp.c   29 Mar 2004 18:16:28 -0000
***************
*** 3261,3267 ****
                                break;
  
                        case DTK_YEAR:
!                               result = tm->tm_year;
                                break;
  
                        case DTK_DECADE:
--- 3261,3271 ----
                                break;
  
                        case DTK_YEAR:
!                               if (tm->tm_year > 0)
!                                       result = tm->tm_year;
!                               else
!                                       /* there is no year 0, just 1 BC and 1 AD*/
!                                       result = tm->tm_year - 1;       
                                break;
  
                        case DTK_DECADE:
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Reply via email to