On Thu, Jul 21, 2011 at 08:40:11AM +0000, Artem Andreev wrote: > > The following bug has been logged online: > > Bug reference: 6126 > Logged by: Artem Andreev > Email address: ar...@oktetlabs.ru > PostgreSQL version: 9.0.4 > Operating system: Debian 6.0 > Description: CC parameter in to_char() behaves incorrectly > Details: > > CC parameter in to_char() behaves incorrectly with BC dates: > > 1st century BC: > > select to_char('0002-01-01 00:00:00 BC' :: timestamp, 'CC'); > ====> > to_char > --------- > 01 > > 2nd century BC: > > select to_char('0101-01-01 00:00:00 BC' :: timestamp, 'CC'); > =====> > to_char > --------- > 00 > > 3rd century BC: > iliran=> select to_char('0301-01-01 00:00:00 BC' :: timestamp, 'CC'); > to_char > --------- > -2 > > In all these cases EXTRACT(CENTURY FROM ...) yields the expected result
I have developed the attached patch which fixes this bug: test=> select to_char('0002-01-01 00:00:00 BC' :: timestamp, 'CC'); to_char --------- -1 (1 row) test=> select to_char('0101-01-01 00:00:00 BC' :: timestamp, 'CC'); to_char --------- -2 (1 row) test=> select to_char('0301-01-01 00:00:00 BC' :: timestamp, 'CC'); to_char --------- -4 (1 row) 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? -- 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..6550296 *** 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 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
-- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs