Thread

Re: Problems with avg on interval data type

Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-05-18T23:30:14Z

> >   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.

Yeah, I agree. The ugliness is that an unsigned integer has been
accepted in the past as "seconds", and would seem to be a reasonable
assumption.

> 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?

Single-field signed integers (and unsigned integers?) must be acceptable
for a time zone specification (pretty sure this is covered in the SQL
spec). Remember that SQL is woefully inadequate for date, time, and time
zone support, but afaicr a signed integer is one of the few things they
do specify ;)

                      - Thomas