BUG #1768: to_char result of an interval differs between 7.x and 8.x

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #1768: to_char result of an interval differs between 7.x and 8.x

michael.oeztuerk

The following bug has been logged online:

Bug reference:      1768
Logged by:          
Email address:      [hidden email]
PostgreSQL version: 8.0.3
Operating system:   Linux / Debian (Version 3.0)
Description:        to_char result of an interval differs between 7.x and
8.x
Details:

When using the following SQL statement the result of a 8.0.3 seems to be
wrong.

Statement: "select to_char(interval '15h 2m 12s', 'YYYYMMDD HH24:MI:SS')"
Result of a 8.0.3: "00010000 15:02:12"
The error in the Result is that it´s "one year behind".

The same statement given to a 7.3.4 delivers the correct result: "00000000
15:02:12"

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: BUG #1768: to_char result of an interval differs between 7.x and 8.x

Álvaro Herrera
On Thu, Jul 14, 2005 at 08:01:11AM +0100, [hidden email] wrote:

> When using the following SQL statement the result of a 8.0.3 seems to be
> wrong.
>
> Statement: "select to_char(interval '15h 2m 12s', 'YYYYMMDD HH24:MI:SS')"
> Result of a 8.0.3: "00010000 15:02:12"
> The error in the Result is that it´s "one year behind".

Yeah, it's strange:

alvherre=# select to_char(interval '15h 2m 12s', 'YYYY-MM-DD HH24:MI:SS');
       to_char      
---------------------
 0001-00-00 15:02:12
(1 fila)

alvherre=# select to_char(interval '15h 2m 12s', 'CCYY-MM-DD HH24:MI:SS');
       to_char      
---------------------
 0101-00-00 15:02:12
(1 fila)

alvherre=# select version();
                                           version                                            
----------------------------------------------------------------------------------------------
 PostgreSQL 8.1devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.6 (Debian 1:3.3.6-7)
(1 fila)


On 7.4 however the year stays at 0, but centuries seem wrong too:

alvherre=# select to_char(interval '15h 2m 12s', 'CCYY-MM-DD HH24:MI:SS');
       to_char      
---------------------
 0100-00-00 15:02:12
(1 row)

alvherre=# select version();
                                          version                                          
--------------------------------------------------------------------------------------------
 PostgreSQL 7.4.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-12)
(1 row)


--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"The problem with the future is that it keeps turning into the present"
(Hobbes)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: BUG #1768: to_char result of an interval differs between

Bruce Momjian-2
Alvaro Herrera wrote:

> On Thu, Jul 14, 2005 at 08:01:11AM +0100, [hidden email] wrote:
>
> > When using the following SQL statement the result of a 8.0.3 seems to be
> > wrong.
> >
> > Statement: "select to_char(interval '15h 2m 12s', 'YYYYMMDD HH24:MI:SS')"
> > Result of a 8.0.3: "00010000 15:02:12"
> > The error in the Result is that it??s "one year behind".
>
> Yeah, it's strange:

Wow, the to_char(interval) code was worse than I thought. I just
committed these changes:

        o Fix to_char(interval) to return proper year and century values.
        o Fix to_char(interval) to return large year/month/day/hour values that
          are larger than possible timestamp values.
        o Prevent to_char(interval) format specifications that make no sense,
          like Month.
        o Clean up formatting.c code to more logically handle return lengths.

I think we agreed that to_char(interval) is fixable and that no better
solution has been proposed, so we are going to have to announce that in
the 8.1 release notes (in 8.0 we said we were going to remove it).

I have implemented this TODO:

        * Prevent to_char() on interval from returning meaningless values
       
          For example, to_char('1 month', 'mon') is meaningless.  Basically,
          most date-related parameters to to_char() are meaningless for
          intervals because interval is not anchored to a date.


and it now shows proper return values:
       
        test=> select to_char(interval '15h 2m 12s', 'YYYY-MM-DD HH24:MI:SS');
               to_char
        ---------------------
         0000-00-00 15:02:12
        (1 row)

and
       
        test=> select to_char(interval '-1000000 year -1000 month -9991 day 19999 hour 650 minute', 'YYYY-MM-DD HH:MI:SS');
                    to_char
        -------------------------------
         -1000083--4--9991 20009:50:00
        (1 row)

Not pretty, but it is accurate.


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


>
> alvherre=# select to_char(interval '15h 2m 12s', 'YYYY-MM-DD HH24:MI:SS');
>        to_char      
> ---------------------
>  0001-00-00 15:02:12
> (1 fila)
>
> alvherre=# select to_char(interval '15h 2m 12s', 'CCYY-MM-DD HH24:MI:SS');
>        to_char      
> ---------------------
>  0101-00-00 15:02:12
> (1 fila)
>
> alvherre=# select version();
>                                            version                                            
> ----------------------------------------------------------------------------------------------
>  PostgreSQL 8.1devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.6 (Debian 1:3.3.6-7)
> (1 fila)
>
>
> On 7.4 however the year stays at 0, but centuries seem wrong too:
>
> alvherre=# select to_char(interval '15h 2m 12s', 'CCYY-MM-DD HH24:MI:SS');
>        to_char      
> ---------------------
>  0100-00-00 15:02:12
> (1 row)
>
> alvherre=# select version();
>                                           version                                          
> --------------------------------------------------------------------------------------------
>  PostgreSQL 7.4.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-12)
> (1 row)
>
>
> --
> Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
> "The problem with the future is that it keeps turning into the present"
> (Hobbes)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  [hidden email]               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match