On Fri, Aug 3, 2012 at 06:51:45PM -0400, Bruce Momjian wrote: > I also tested boundry values, e.g. 6th Century BC is 600-501: > > test=> select to_char('0600-01-01 00:00:00 BC' :: timestamp, 'CC'); > to_char > --------- > -6 > (1 row) > > test=> select to_char('0599-01-01 00:00:00 BC' :: timestamp, 'CC'); > to_char > --------- > -6 > (1 row) > > test=> select to_char('0501-01-01 00:00:00 BC' :: timestamp, 'CC'); > to_char > --------- > -6 > (1 row) > > test=> select to_char('0500-01-01 00:00:00 BC' :: timestamp, 'CC'); > to_char > --------- > -5 > (1 row) > > 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. -- 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..7eb573d *** 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 *** 3473,3487 **** { 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) { --- 3480,3504 ---- { 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; } else if (tmfc.cc) /* use first year of century */ ! { ! if (tmfc.cc >= 0) ! tm->tm_year = (tmfc.cc - 1) * 100 + 1; ! else ! tm->tm_year = tmfc.cc * 100 + 1; ! } if (tmfc.bc) { 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-04 10:23:51.000000000 -0400 --- /rtmp/good.out 2012-08-04 10:25:41.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');
-- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs