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@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
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:
