Re: [PATCHES] [HACKERS] Dates BC.
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.
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.
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