Обсуждение: BUG #6126: CC parameter in to_char() behaves incorrectly
The following bug has been logged online: Bug reference: 6126 Logged by: Artem Andreev Email address: artem@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
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: artem@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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Вложения
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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Вложения
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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Вложения
On Mon, Aug 6, 2012 at 03:20:18PM -0400, Bruce Momjian wrote: > 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. Applied. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +