On Sat, Aug 4, 2012 at 10:34:14AM -0400, Bruce Momjian wrote: > > I am thinking it is too late to apply this for 9.2 because users might > > have already tested their applications, though I doubt many are using BC > > dates. Feedback? > > There is never just one bug in formatting.c --- the input side was also > broken for BC/negative centuries. The attached patch fixes the input > side as well, and shows the old/fixed output for BC centuries. The test > queries were: > > SELECT to_date('-6 4', 'CC YY'); > SELECT to_date(' 6 4', 'CC YY'); > SELECT to_date('-6 00', 'CC YY'); > SELECT to_date(' 6 00', 'CC YY'); > SELECT to_date('-6', 'CC'); > SELECT to_date(' 6', 'CC'); > > I believe this is all for 9.3-only.
OK, I found another bug in our code; the output of: SELECT to_date(' 6 BC', 'CC BC'); returned 501BC, instead of the start of the century, 600BC, like SELECT to_date('-6', 'CC') does. I also allowed negative BC dates to map to AD dates, just like negative AD dates map to BC dates. Attached is an updated patch and output diff. -- Bruce Momjian <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c new file mode 100644 index 4347ad3..6bd3fc6 *** a/src/backend/utils/adt/formatting.c --- b/src/backend/utils/adt/formatting.c *************** DCH_to_char(FormatNode *node, bool is_in *** 2640,2647 **** case DCH_CC: if (is_interval) /* straight calculation */ i = tm->tm_year / 100; ! else /* century 21 starts in 2001 */ ! i = (tm->tm_year - 1) / 100 + 1; if (i <= 99 && i >= -99) sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : 2, i); else --- 2640,2654 ---- case DCH_CC: if (is_interval) /* straight calculation */ i = tm->tm_year / 100; ! else ! { ! if (tm->tm_year > 0) ! /* Century 20 == 1901 - 2000 */ ! i = (tm->tm_year - 1) / 100 + 1; ! else ! /* Century 6BC == 600BC - 501BC */ ! i = tm->tm_year / 100 - 1; ! } if (i <= 99 && i >= -99) sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : 2, i); else *************** do_to_timestamp(text *date_txt, text *fm *** 3471,3497 **** */ if (tmfc.cc && tmfc.yysz <= 2) { tm->tm_year = tmfc.year % 100; if (tm->tm_year) ! tm->tm_year += (tmfc.cc - 1) * 100; else ! tm->tm_year = tmfc.cc * 100; } else tm->tm_year = tmfc.year; } else if (tmfc.cc) /* use first year of century */ - tm->tm_year = (tmfc.cc - 1) * 100 + 1; - - if (tmfc.bc) { ! if (tm->tm_year > 0) ! tm->tm_year = -(tm->tm_year - 1); else ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_DATETIME_FORMAT), ! errmsg("inconsistent use of year %04d and \"BC\"", ! tm->tm_year))); } if (tmfc.j) --- 3478,3511 ---- */ if (tmfc.cc && tmfc.yysz <= 2) { + if (tmfc.bc) + tmfc.cc = -tmfc.cc; tm->tm_year = tmfc.year % 100; if (tm->tm_year) ! { ! if (tmfc.cc >= 0) ! tm->tm_year += (tmfc.cc - 1) * 100; ! else ! tm->tm_year = (tmfc.cc + 1) * 100 - tm->tm_year + 1; ! } else ! tm->tm_year = tmfc.cc * 100 + ((tmfc.cc >= 0) ? 0 : 1); } else + { tm->tm_year = tmfc.year; + if (tmfc.bc && tm->tm_year > 0) + tm->tm_year = -(tm->tm_year - 1); + } } else if (tmfc.cc) /* use first year of century */ { ! if (tmfc.bc) ! tmfc.cc = -tmfc.cc; ! if (tmfc.cc >= 0) ! tm->tm_year = (tmfc.cc - 1) * 100 + 1; else ! tm->tm_year = tmfc.cc * 100 + 1; } if (tmfc.j) diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out new file mode 100644 index ab8faab..db2cfe6 *** a/src/test/regress/expected/timestamp.out --- b/src/test/regress/expected/timestamp.out *************** SELECT '' AS to_char_3, to_char(d1, 'Y,Y *** 992,998 **** | 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494 | 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495 | 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496 ! | 0,097 0097 097 97 7 01 1 02 07 047 16 3 1686042 | 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536 | 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157 | 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778 --- 992,998 ---- | 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494 | 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495 | 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496 ! | 0,097 0097 097 97 7 -1 1 02 07 047 16 3 1686042 | 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536 | 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157 | 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778 *************** SELECT '' AS to_char_4, to_char(d1, 'FMY *** 1063,1069 **** | 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494 | 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495 | 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496 ! | 0,097 97 97 97 7 1 1 2 7 47 16 3 1686042 | 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536 | 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157 | 1,097 1097 97 97 7 11 1 2 7 47 16 3 2121778 --- 1063,1069 ---- | 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494 | 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495 | 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496 ! | 0,097 97 97 97 7 -1 1 2 7 47 16 3 1686042 | 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536 | 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157 | 1,097 1097 97 97 7 11 1 2 7 47 16 3 2121778 diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out new file mode 100644 index 9a4ce3e..6581b5e *** a/src/test/regress/expected/timestamptz.out --- b/src/test/regress/expected/timestamptz.out *************** SELECT '' AS to_char_3, to_char(d1, 'Y,Y *** 1074,1080 **** | 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494 | 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495 | 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496 ! | 0,097 0097 097 97 7 01 1 02 07 047 16 3 1686042 | 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536 | 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157 | 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778 --- 1074,1080 ---- | 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494 | 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495 | 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496 ! | 0,097 0097 097 97 7 -1 1 02 07 047 16 3 1686042 | 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536 | 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157 | 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778 *************** SELECT '' AS to_char_4, to_char(d1, 'FMY *** 1146,1152 **** | 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494 | 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495 | 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496 ! | 0,097 97 97 97 7 1 1 2 7 47 16 3 1686042 | 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536 | 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157 | 1,097 1097 97 97 7 11 1 2 7 47 16 3 2121778 --- 1146,1152 ---- | 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494 | 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495 | 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496 ! | 0,097 97 97 97 7 -1 1 2 7 47 16 3 1686042 | 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536 | 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157 | 1,097 1097 97 97 7 11 1 2 7 47 16 3 2121778
*** /rtmp/bad.out 2012-08-06 14:31:12.000000000 -0400 --- /rtmp/good.out 2012-08-06 14:30:26.000000000 -0400 *************** *** 1,7 **** SELECT to_date('-6 4', 'CC YY'); to_date --------------- ! 0697-01-01 BC (1 row) SELECT to_date(' 6 4', 'CC YY'); --- 1,7 ---- SELECT to_date('-6 4', 'CC YY'); to_date --------------- ! 0504-01-01 BC (1 row) SELECT to_date(' 6 4', 'CC YY'); *************** *** 13,19 **** SELECT to_date('-6 00', 'CC YY'); to_date --------------- ! 0601-01-01 BC (1 row) SELECT to_date(' 6 00', 'CC YY'); --- 13,19 ---- SELECT to_date('-6 00', 'CC YY'); to_date --------------- ! 0600-01-01 BC (1 row) SELECT to_date(' 6 00', 'CC YY'); *************** *** 25,31 **** SELECT to_date('-6', 'CC'); to_date --------------- ! 0700-01-01 BC (1 row) SELECT to_date(' 6', 'CC'); --- 25,31 ---- SELECT to_date('-6', 'CC'); to_date --------------- ! 0600-01-01 BC (1 row) SELECT to_date(' 6', 'CC'); *************** *** 35,41 **** (1 row) SELECT to_date('-6 4 BC', 'CC YY BC'); ! ERROR: inconsistent use of year -696 and "BC" SELECT to_date(' 6 4 BC', 'CC YY BC'); to_date --------------- --- 35,45 ---- (1 row) SELECT to_date('-6 4 BC', 'CC YY BC'); ! to_date ! ------------ ! 0504-01-01 ! (1 row) ! SELECT to_date(' 6 4 BC', 'CC YY BC'); to_date --------------- *************** *** 43,49 **** (1 row) SELECT to_date('-6 00 BC', 'CC YY BC'); ! ERROR: inconsistent use of year -600 and "BC" SELECT to_date(' 6 00 BC', 'CC YY BC'); to_date --------------- --- 47,57 ---- (1 row) SELECT to_date('-6 00 BC', 'CC YY BC'); ! to_date ! ------------ ! 0600-01-01 ! (1 row) ! SELECT to_date(' 6 00 BC', 'CC YY BC'); to_date --------------- *************** *** 51,60 **** (1 row) SELECT to_date('-6 BC', 'CC BC'); ! ERROR: inconsistent use of year -699 and "BC" SELECT to_date(' 6 BC', 'CC BC'); to_date --------------- ! 0501-01-01 BC (1 row) --- 59,72 ---- (1 row) SELECT to_date('-6 BC', 'CC BC'); ! to_date ! ------------ ! 0501-01-01 ! (1 row) ! SELECT to_date(' 6 BC', 'CC BC'); to_date --------------- ! 0600-01-01 BC (1 row)
-- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs