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

Re: Problems with avg on interval data type

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: lockhart(at)fourpalms(dot)org
Cc: jeremy(at)horizonlive(dot)com, pgsql-bugs(at)postgresql(dot)org, Hackers List <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Problems with avg on interval data type
Date: 2001-05-18 16:55:55
Message-ID: 20226.990204955@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> writes:
>> We have recently upgraded from 7.0.3 to 7.1 and a query which used
>> to work is no longer working.
>> The query does an avg on an interval column and now gets the error:
>> ERROR:  Bad interval external representation '0'

> OK, there is one case of interval constant which is not handled
> correctly in the 7.1.x release -- the simplest interval specification
> having only an unadorned integer. That is a bug, for which I have a
> patch (or patches) available.

I have modified the declaration of avg(interval) to initialize its
accumulator as '0 second' instead of just '0', so as far as the
aggregate goes it's not necessary to consider this a bug.

> Currently, we interpret various forms as follows:

>   Value	Units
>   +8		hours
>   -8		hours
>   8.0		seconds
>   8		?? seconds ??

> I would propose that the last example should be interpreted in units of
> seconds, but that could be perilously close to the conventions required
> for the signed examples. Comments?

Yipes.  I do not like the idea that '+8' and '8' yield radically
different results.  That's definitely going to create unhappiness.

I suggest that the current code is more correct than you think ;-).
ISTM it is a good idea to require a units field, or at least some
punctuation giving a clue about units --- for example I do not object to
'08:00' being interpreted as hours and minutes.  But I would be inclined
to reject all four of the forms '+8', '-8', '8.0', and '8' as ambiguous.
Is there something in the SQL spec that requires us to accept them?

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Oleg BartunovDate: 2001-05-18 17:10:10
Subject: Re: Plans for solving the VACUUM problem
Previous:From: Bruce MomjianDate: 2001-05-18 16:54:08
Subject: Re: [PATCHES] syntax warning on

pgsql-bugs by date

Next:From: Krzysztof GajdemskiDate: 2001-05-18 18:12:34
Subject: Re: "Unvisible" duplicates in SELECT [...] HAVING count(attrib) > 1
Previous:From: Thomas LockhartDate: 2001-05-18 16:47:10
Subject: Re: Problems with avg on interval data type

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