BUG #6126: CC parameter in to_char() behaves incorrectly

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. +