Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Oliver Elphick <olly@lfix.co.uk>
Subject: Re: A proposal for Interval Math
Date: Sun, 19 May 2002 14:21:05 -0700
Cc: pgsql-sql@postgresql.org
References: <200205191336.10383.josh@agliodbs.com>
	<1021841886.1401.56.camel@linda>
In-Reply-To: <1021841886.1401.56.camel@linda>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200205191421.05691.josh@agliodbs.com>


Oliver,

> As I understand the standard, this is an illegal operation, because the
> two intervals are of incompatible types and a meaningful result is not
> possible.  I do not think anyone should try to implement it.

The reason I propose it is that regardless of the standard, PostgreSQL user=
s=20
need to, for example, calculate the number of 2-week intervals in a given=
=20
interval column all the time.   Right now, everybody is using the Date data=
=20
type and integers, which has some significant shortcomings.=20=20

I'm  a big proponent of standards, and you will find me on this and other=
=20
forums pushing sticking to the standard as a #1 priority.   However, in thi=
s=20
case, the standard leaves DBAs with no alternative.  SQL92 neither allows m=
e=20
to divide unlike interval units, nor to convert unlike interval units into=
=20
like units.  Either I'm misreading it, or the ANSI committee really painted=
=20
us into a corner on this one.

I would be quite positive to an argument that elimintates the second=20
restriction but not the first, for example:
'7 months' / '3 days' =3D ERROR,   but
interval_round('7 months','days') / '3 days' =3D 70

However, I'll continue pushing for some solution that allows me to calculat=
e=20
pay periods in real intervals.

--=20
-Josh Berkus