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: | 3B05520E.B0431B1A@alumni.caltech.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-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.
'05:00').
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'
and
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
context
INTERVAL '-8'
must be interpreted as having units of "hours", while our historical
usage has
INTERVAL '8'
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-05-18 16:55:55 | Re: Problems with avg on interval data type |
Previous Message | Tom Lane | 2001-05-18 16:08:07 | Re: Problems with avg on interval data type |
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Lockhart | 2001-05-18 16:49:17 | Re: [PATCHES] syntax warning on |
Previous Message | Tom Lane | 2001-05-18 16:38:22 | Re: Plans for solving the VACUUM problem |