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: [email protected]
> 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 <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
