The following bug has been logged online:

Bug reference: 2885
Logged by: Akio Iwaasa
Email address: iwaasa@mxs.nes.nec.co.jp
PostgreSQL version: 8.2.1
Operating system: Redhat EL ES 3.0
Description: to_char function
Details:

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)

Regards.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"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

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. +

---------------------------(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> 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

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

"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

---------------------------(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

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>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings