BUG #2885: to_char function

"Akio Iwaasa" <iwaasa@mxs.nes.nec.co.jp> writes:

When I've used "to_char" function to convert "Date" to
"century" format text, '2000-01-01' was converted in
the 21st century.

postgres=# select to_char('2000-01-01'::date, 'CC') ;
to_char
---------
21
(1 row)

I'm not entirely sure that this is wrong, because of the behavior of
the Y and YY fields.

regression=# select to_char('2007-01-01'::date, 'CC YY') ;
to_char
---------
21 07
(1 row)

regression=# select to_date('21 07', 'CC YY');
to_date
------------
2007-01-01
(1 row)

regression=# select to_char('2000-01-01'::date, 'CC YY') ;
to_char
---------
21 00
(1 row)

regression=# select to_date('21 00', 'CC YY');
to_date
------------
2000-01-01
(1 row)

If we make CC treat 2000 as being in the 20th century, what should YY
do?

Perhaps more to the point, how do these things act in Oracle?
to_char is basically an Oracle-compatibility function so we should
adopt their bugs :-(

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

If we make CC treat 2000 as being in the 20th century, what should YY
do?

Report "00", IMVHO. There's no year "0" so year 1 AD is "CC=1, YY=1". Year
100 is "CC=1, YY=00", year 1000 is "CC=10, YY=00", year 2000 is "CC=20, YY=00"
(1900 is "CC=19, YY=00"). Centuries change on years ending with '01', years
ending with '00' are from the previous century :-)

But for millenia the thing is diffent... ;-) (Just to complicate a bit more)

Perhaps more to the point, how do these things act in Oracle?
to_char is basically an Oracle-compatibility function so we should
adopt their bugs :-(

I can't answer that... But my expectations are like Akio's.

--
Jorge Godoy <jgodoy@gmail.com>

I updated our documentation for CC:

<entry>century component of year (minimum 2 digits)</entry>

---------------------------------------------------------------------------

Tom Lane wrote:

"Akio Iwaasa" <iwaasa@mxs.nes.nec.co.jp> writes:

When I've used "to_char" function to convert "Date" to
"century" format text, '2000-01-01' was converted in
the 21st century.

postgres=# select to_char('2000-01-01'::date, 'CC') ;
to_char
---------
21
(1 row)

I'm not entirely sure that this is wrong, because of the behavior of
the Y and YY fields.

regression=# select to_char('2007-01-01'::date, 'CC YY') ;
to_char
---------
21 07
(1 row)

regression=# select to_date('21 07', 'CC YY');
to_date
------------
2007-01-01
(1 row)

regression=# select to_char('2000-01-01'::date, 'CC YY') ;
to_char
---------
21 00
(1 row)

regression=# select to_date('21 00', 'CC YY');
to_date
------------
2000-01-01
(1 row)

If we make CC treat 2000 as being in the 20th century, what should YY
do?

Perhaps more to the point, how do these things act in Oracle?
to_char is basically an Oracle-compatibility function so we should
adopt their bugs :-(

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce@momjian.us> writes:

I updated our documentation for CC:
<entry>century component of year (minimum 2 digits)</entry>

Updating the documentation isn't a solution; either the code is correct
or it's not, and I rather suspect it's not (it's incompatible with
EXTRACT(CENTURY) for example). Can someone check Oracle's behavior?

regards, tom lane

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the OLAP option
JServer Release 9.2.0.4.0 - Production

select ordh_dtinral, to_char(ordh_dtinral,'CC YY') from ordheader
where ordh_dtinral between '01-Jan-2000' and '01-Mar-2000';

ORDH_DTIN TO_CH
--------- -----
05-JAN-00 20 00
05-JAN-00 20 00
05-JAN-00 20 00
05-JAN-00 20 00
05-JAN-00 20 00
05-JAN-00 20 00
05-JAN-00 20 00
05-JAN-00 20 00
06-JAN-00 20 00
06-JAN-00 20 00
06-JAN-00 20 00

(results truncated)

On Jan 12, 2007, at 3:12 PM, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

I updated our documentation for CC:
<entry>century component of year (minimum 2 digits)</entry>

Updating the documentation isn't a solution; either the code is
correct
or it's not, and I rather suspect it's not (it's incompatible with
EXTRACT(CENTURY) for example). Can someone check Oracle's behavior?

regards, tom lane

---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that
your
message can get through to the mailing list cleanly

Douglas Toltzman
doug@oakstreetsoftware.com
(910) 526-5938

Douglas Toltzman <doug@oakstreetsoftware.com> writes:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the OLAP option
JServer Release 9.2.0.4.0 - Production

select ordh_dtinral, to_char(ordh_dtinral,'CC YY') from ordheader
where ordh_dtinral between '01-Jan-2000' and '01-Mar-2000';

ORDH_DTIN TO_CH
--------- -----
05-JAN-00 20 00
05-JAN-00 20 00

Thanks --- looks like we'd better change it. I'll work on it later today.

regards, tom lane

"Akio Iwaasa" <iwaasa@mxs.nes.nec.co.jp> writes:

When I've used "to_char" function to convert "Date" to
"century" format text, '2000-01-01' was converted in
the 21st century.

I've patched this in HEAD and 8.2 branch.

regards, tom lane