Обсуждение: BUG #2885: to_char function
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.
"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
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
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>