Re: [PATCHES] [HACKERS] Dates BC.

2004-03-29 Thread Bruce Momjian
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', '/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, '/MM/DD AD');
 to_char
  ---
  /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, '/MM/DD AD');
to_char
---
 0002/01/01 AD
(1 row)

test= select to_char('0001-01-01 AD'::date, '/MM/DD AD');
to_char
---
 0001/01/01 AD
(1 row)

test= select to_char('0001-01-01 BC'::date, '/MM/DD AD');
to_char
---
 /01/01 AD **error in year and AD**
(1 row)

test= select to_char('0002-01-01 BC'::date, '/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, '/MM/DD AD');
to_char
---
 0002/01/01 AD
(1 row)

test= select to_char('0001-01-01 AD'::date, '/MM/DD AD');
to_char
---
 0001/01/01 AD
(1 row)

test= select to_char('0001-01-01 BC'::date, '/MM/DD AD');
to_char
---
 0001/01/01 BC
(1 row)

test= select to_char('0002-01-01 BC'::date, '/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 -   1.72
--- src/backend/utils/adt/formatting.c  29 Mar 2004 18:16:26 -
***
*** 169,175 
   * AC / DC
   * --
   */
! #define 

Re: [PATCHES] [HACKERS] Dates BC.

2004-03-29 Thread Bruce Momjian
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.

I have also add a doc mention to my patch that mentions that there is no
0 AD, and therefore subtraction of BC years from AD years must be done
with caution.

-- 
  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: doc/src/sgml/func.sgml
===
RCS file: /cvsroot/pgsql-server/doc/src/sgml/func.sgml,v
retrieving revision 1.195
diff -c -c -r1.195 func.sgml
*** doc/src/sgml/func.sgml  19 Mar 2004 19:13:26 -  1.195
--- doc/src/sgml/func.sgml  29 Mar 2004 20:31:28 -
***
*** 5216,5223 
termliteralweek/literal/term
listitem
 para
! The number of
! the week of the year that the day is in.  By definition
  (acronymISO/acronym 8601), the first week of a year
  contains January 4 of that year.  (The acronymISO/acronym-8601
  week starts on Monday.)  In other words, the first Thursday of
--- 5216,5222 
termliteralweek/literal/term
listitem
 para
! The number of the week of the year that the day is in.  By definition
  (acronymISO/acronym 8601), the first week of a year
  contains January 4 of that year.  (The acronymISO/acronym-8601
  week starts on Monday.)  In other words, the first Thursday of
***
*** 5235,5241 
termliteralyear/literal/term
listitem
 para
! The year field
 /para
  
  screen
--- 5234,5241 
termliteralyear/literal/term
listitem
 para
! The year field.  Keep in mind there is no literal0 AD/, so subtracting 
! literalBC/ years from literalAD/ years should be done with care.
 /para
  
  screen
Index: src/backend/utils/adt/datetime.c
===
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/datetime.c,v
retrieving revision 1.125
diff -c -c -r1.125 datetime.c
*** src/backend/utils/adt/datetime.c25 Feb 2004 19:41:23 -  1.125
--- src/backend/utils/adt/datetime.c29 Mar 2004 20:31:34 -
***
*** 93,99 
{acsst, DTZ, POS(42)},/* Cent. Australia */
{acst, DTZ, NEG(16)}, /* Atlantic/Porto Acre Summer Time */
{act, TZ, NEG(20)},   /* Atlantic/Porto Acre Time */
!   {DA_D, ADBC, AD},   /* ad for years = 0 */
{adt, DTZ, NEG(12)},  /* Atlantic Daylight Time */
{aesst, DTZ, POS(44)},/* E. Australia */
{aest, TZ, POS(40)},  /* Australia Eastern Std Time */
--- 93,99 
{acsst, DTZ, POS(42)},/* Cent. Australia */
{acst, DTZ, NEG(16)}, /* Atlantic/Porto Acre Summer Time */
{act, TZ, NEG(20)},   /* Atlantic/Porto Acre Time */
!   {DA_D, ADBC, AD},   /* ad for years  0 */
{adt, DTZ, NEG(12)},  /* Atlantic Daylight Time */
{aesst, DTZ, POS(44)},/* E. Australia */
{aest, TZ, POS(40)},  /* Australia Eastern Std Time */
***
*** 139,145 
{azot, TZ, NEG(4)},   /* Azores Time */
{azst, DTZ, POS(20)}, /* Azerbaijan Summer Time */
{azt, TZ, POS(16)},   /* Azerbaijan Time */
!   {DB_C, ADBC, BC},   /* bc for years  0 */
{bdst, TZ, POS(8)},   /* British Double Summer Time */
{bdt, TZ, POS(24)},   /* Dacca */
{bnt, TZ, POS(32)},   /* Brunei Darussalam Time */
--- 139,145 
{azot, TZ, NEG(4)},   /* Azores Time */
{azst, DTZ, POS(20)}, /* Azerbaijan Summer Time */
{azt, TZ, POS(16)},   /* Azerbaijan Time */
!   {DB_C, ADBC, BC},   /* bc for years = 0 */
{bdst, TZ, POS(8)},   /* British Double Summer Time */
{bdt, TZ, POS(24)},   /* Dacca */
{bnt, TZ, POS(32)},   /* Brunei Darussalam Time */
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 -   1.72
--- src/backend/utils/adt/formatting.c  

Re: [PATCHES] [HACKERS] Dates BC.

2004-03-29 Thread Karel Zak
On Mon, Mar 29, 2004 at 03:37:07PM -0500, Bruce Momjian wrote:
 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.
 
 I have also add a doc mention to my patch that mentions that there is no
 0 AD, and therefore subtraction of BC years from AD years must be done
 with caution.

 The patch seems good for me. Thanks.

Karel

-- 
 Karel Zak  [EMAIL PROTECTED]
 http://home.zf.jcu.cz/~zakkr/

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match