Re: interval bug?

On Mon, 24 Dec 2001, Thomas Lockhart wrote:

> (resent due to dns trouble)
>
> > select extract(YEAR FROM debut) as annee,...
> > EXTRACT(EPOCH FROM avg(fin - debut))
> > from radlog where fin is not null;
> > Fails with '0' bas interval external representation for any debut = fin.
> > This can also be seen with select now() - now();
>
> I'm not seeing this symptom exactly as you describe (and your query
> fails due to lack of a group by clause, right?). However, I *do* see a
Yeah!! That was left as an exercise :)
> problem on 7.1 with aggregates on intervals, seemingly due to the values
> used to initialize the aggregate. Fix is below. I don't see the same
> trouble in 7.2.
>
> > This is with 7.1.3
> > Is there any quick patch for 7.1.3
>
> Tested on 7.1:
>
> thomas=# select extract(year from debut) as annee,
> thomas-# avg(fin-debut) as total from radlog group by annee;
> ERROR: Bad interval external representation '0'
> thomas=# update pg_aggregate set agginitval='{\'0 sec\',\'0 sec\'}'
> thomas-# where aggname = 'avg' and aggbasetype=1186;
> UPDATE 1
Yep!! It works ...

Thank you so much...
> thomas=# select extract(year from debut) as annee, avg(fin-debut) as
> total from radlog group by annee;
> annee | total
> -------+------------------
> 2001 | @ 3 mins 38 secs
>
I started Xmas night so I can't go any further.

But I hope all of you guys have a nice time with family and/or friends

Best regards from France
--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp(at)pyrenet(dot)fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)