Skip site navigation (1) Skip section navigation (2)

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

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Cc: michael(dot)oeztuerk(at)haufe(dot)de, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1768: to_char result of an interval differs between
Date: 2005-08-18 04:41:59
Message-ID: 200508180441.j7I4fxO12018@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Alvaro Herrera wrote:
> On Thu, Jul 14, 2005 at 08:01:11AM +0100, michael(dot)oeztuerk(at)haufe(dot)de 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
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

pgsql-bugs by date

Next:From: Michael FuhrDate: 2005-08-18 05:37:58
Subject: Re: BUG #1831: plperl gives error after reconnect.
Previous:From: Tom LaneDate: 2005-08-18 04:26:28
Subject: Re: BUG #1831: plperl gives error after reconnect.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group