Re: [BUGS] BUG #6126: CC parameter in to_char() behaves incorrectly
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 br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6126: CC parameter in to_char() behaves incorrectly
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 br...@momjian.ushttp://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..6bd3fc6
*** 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
*** do_to_timestamp(text *date_txt, text *fm
*** 3471,3497
*/
if (tmfc.cc tmfc.yysz = 2)
{
tm-tm_year = tmfc.year % 100;
if (tm-tm_year)
! tm-tm_year += (tmfc.cc - 1) * 100;
else
! tm-tm_year = tmfc.cc * 100;
}
else
tm-tm_year = tmfc.year;
}
else if (tmfc.cc) /* use first year of century */
- tm-tm_year = (tmfc.cc - 1) * 100 + 1;
-
- if (tmfc.bc)
{
! if (tm-tm_year 0)
! tm-tm_year = -(tm-tm_year - 1);
else
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
! errmsg(inconsistent use of year %04d and \BC\,
! tm-tm_year)));
}
if (tmfc.j)
--- 3478,3511
*/
if (tmfc.cc tmfc.yysz = 2)
{
+ if (tmfc.bc)
+ tmfc.cc = -tmfc.cc;
tm-tm_year = tmfc.year % 100;
if (tm-tm_year)
! {
! if (tmfc.cc = 0)
! tm-tm_year += (tmfc.cc - 1) * 100;
! else
! tm-tm_year = (tmfc.cc + 1) * 100 - tm-tm_year + 1;
! }
else
! tm-tm_year = tmfc.cc * 100 + ((tmfc.cc = 0) ? 0 : 1);
}
else
+ {
tm-tm_year = tmfc.year;
+ if (tmfc.bc tm-tm_year 0)
+ tm-tm_year = -(tm-tm_year - 1);
+ }
}
else if (tmfc.cc) /* use first year of century */
{
! if (tmfc.bc)
! tmfc.cc = -tmfc.cc;
! if (tmfc.cc = 0)
! tm-tm_year = (tmfc.cc - 1) * 100 + 1;
else
! tm-tm_year = tmfc.cc * 100 + 1;
}
if (tmfc.j)
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
Re: [BUGS] BUG #6126: CC parameter in to_char() behaves incorrectly
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 br...@momjian.ushttp://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..7eb573d
*** 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
*** do_to_timestamp(text *date_txt, text *fm
*** 3473,3487
{
tm-tm_year = tmfc.year % 100;
if (tm-tm_year)
! tm-tm_year += (tmfc.cc - 1) * 100;
else
! tm-tm_year = tmfc.cc * 100;
}
else
tm-tm_year = tmfc.year;
}
else if (tmfc.cc) /* use first year of century */
! tm-tm_year = (tmfc.cc - 1) * 100 + 1;
if (tmfc.bc)
{
--- 3480,3504
{
tm-tm_year = tmfc.year % 100;
if (tm-tm_year)
! {
! if (tmfc.cc = 0)
! tm-tm_year += (tmfc.cc - 1) * 100;
! else
! tm-tm_year = (tmfc.cc + 1) * 100 - tm-tm_year + 1;
! }
else
! tm-tm_year = tmfc.cc * 100 + ((tmfc.cc = 0) ? 0 : 1);
}
else
tm-tm_year = tmfc.year;
}
else if (tmfc.cc) /* use first year of century */
! {
! if (tmfc.cc = 0)
! tm-tm_year = (tmfc.cc - 1) * 100 + 1;
! else
! tm-tm_year = tmfc.cc * 100 + 1;
! }
if (tmfc.bc)
{
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
Re: [BUGS] BUG #6126: CC parameter in to_char() behaves incorrectly
On Thu, Jul 21, 2011 at 08:40:11AM +, Artem Andreev wrote:
The following bug has been logged online:
Bug reference: 6126
Logged by: Artem Andreev
Email address: ar...@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 br...@momjian.ushttp://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
[BUGS] 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: ar...@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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
