FAQ

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

Search Discussions

Discussion Posts

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 1 of 6 | next ›
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMay 18, '01 at 4:47p
activeMay 19, '01 at 6:21a
posts6
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase