Skip site navigation (1) Skip section navigation (2)

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

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Artem Andreev <artem(at)oktetlabs(dot)ru>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6126: CC parameter in to_char() behaves incorrectly
Date: 2012-08-03 22:51:45
Message-ID: 20120803225145.GK3463@momjian.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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. +

Attachment: century.diff
Description: text/x-diff (5.1 KB)

In response to

Responses

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2012-08-04 14:34:14
Subject: Re: BUG #6126: CC parameter in to_char() behaves incorrectly
Previous:From: Bruce MomjianDate: 2012-08-03 18:03:21
Subject: Re: BUG #6117: psql -c does not work as expected. a documentation bug? a program bug?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group