Thread: interval bug?

Hi, 

I just felt on this one :

create table radlog (
...
debut timestamp,
fin timestamp,
...)

select extract(YEAR FROM debut) as annee,extract(MONTH FROM debut) as
mois, EXTRACT(EPOCH FROM sum(fin-debut)) as total, 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();

This is with 7.1.3

I've not been yet able to test with .2.

Is there any quick patch for 7.1.3

Merry Xmas to you all

Regards

-- 
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@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)

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@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)