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: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: lockhart(at)fourpalms(dot)org, 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 23:30:14
Message-ID: 3B05B086.E4D34A08@alumni.caltech.edu (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
> >   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

In response to

Responses

pgsql-hackers by date

Next:From: Mikheev, VadimDate: 2001-05-19 00:08:07
Subject: RE: Plans for solving the VACUUM problem
Previous:From: mlwDate: 2001-05-18 23:23:28
Subject: External search engine, advice

pgsql-bugs by date

Next:From: Peter EisentrautDate: 2001-05-19 00:55:12
Subject: Re: Re: Problems with avg on interval data type
Previous:From: Tom LaneDate: 2001-05-18 22:18:31
Subject: Re: select avg() and sum() returns false value

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