Re: 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(at)oktetlabs(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +