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

Re: Problems with avg on interval data type

From: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>
To: jeremy(at)horizonlive(dot)com, pgsql-bugs(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Hackers List <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Problems with avg on interval data type
Date: 2001-05-18 16:47:10
Message-ID: (view raw or whole thread)
Lists: pgsql-bugspgsql-hackers
> 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.

Before I post the patch (which should go into the 7.1.2 release as a bug
fix) I need feedback on a conventions dilemma, which led to the code
modifications which introduced the bug. Here it is:

Intervals usually indicate a time span, and can be specified with either
"# units" strings (e.g. '5 hours') or (as of 7.1) as "hh:mm:ss" (e.g.

A new construct, "a_expr AT TIME ZONE c_expr" is supported in 7.1, per
SQL99 spec. One of the possible arguments is

  a_expr AT TIME ZONE 'PST'


  a_expr AT TIME ZONE INTERVAL '-08:00'

It is this last style which leads to the problem of how to interpret
signed or unsigned integers as interval types. For example, in this


must be interpreted as having units of "hours", while our historical
usage has


being interpreted as "seconds" (even with signed values). 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?

                     - Thomas

In response to


pgsql-hackers by date

Next:From: Thomas LockhartDate: 2001-05-18 16:49:17
Subject: Re: [PATCHES] syntax warning on
Previous:From: Tom LaneDate: 2001-05-18 16:38:22
Subject: Re: Plans for solving the VACUUM problem

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-05-18 16:55:55
Subject: Re: Problems with avg on interval data type
Previous:From: Tom LaneDate: 2001-05-18 16:08:07
Subject: Re: Problems with avg on interval data type

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