Content-Type: text/plain;
  charset="us-ascii"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: pgsql-sql@postgresql.org
Subject: A proposal for Interval Math
Date: Sun, 19 May 2002 13:36:10 -0700
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200205191336.10383.josh@agliodbs.com>

Developers:

What follows is a proposal for a spec for PostgreSQL handling of Interval=
=20
multiplication and divsion.  I freely admit that I can't do much to help=20
implement this spec (my C skills are infantile).  However, I think it would=
=20
be helpful to at least have a roadmap for eventual Interval map support.=20=
=20

In theory, all of the following calculations should be possible using Postg=
res=20
data types:
INTERVAL * NUMBER
INTERVAL / NUMBER
INTERVAL / INTERVAL
However, this has yet to be implemented due partly to the difficult definit=
ion=20
of the above in the SQL92 standard, in which intervals should be divisible =
by=20
floats, and the data type should be "sub-typed", with multiplication and=20
division being possible only for like interval units.=20=20
As far as I know, no RDBMS has been able to implement the full SQL92 standa=
rd=20
for Intervals.  Were I on the committee, I would argue that it is=20
conceptually improbable, and not particularly useful.  Are they accepting=
=20
applicants?  :-)

Therefore, I am going to argue for a slightly altered implementation, with=
=20
adjustments intended to overcome the essential atomic nature of date units=
=20
(i.e. the fact that "0.4 Months" cannot be accurately measured in smaller=
=20
interval units).=20=20

My proposal is this:  that Intervals should be treated in the same way that=
=20
Integer math is handled: with automatic rounding and remainders.  Further,=
=20
Intervals would be multiplied only by Integers, not by floats or numeric=20
values.  This solves a lot of the problems of date unit division by simply=
=20
refusing to perform it.

Thus:
INTERVAL / INTEGER =3D INTERVAL + ( INTERVAL % INTEGER )
INTERVAL * INTEGER =3D INTERVAL=20
INTERVAL / INTERVAL =3D INTEGER + ( INTERVAL % INTERVAL )

In other words:
'7 months' / 2 =3D '3 months'
'7 months' % 2 =3D '1 month'
'7 months' * 2 =3D '14 months'
'7 months' / '3 months' =3D 2
'7 months' % '3 months' =3D 1

Ah, you ask, but what about '7 months' / '3 days'?  This requires the addti=
on=20
of "Interval Rounding" to the Postgresql system.    Any division of unlike=
=20
Interval increments would require Interval Rounding to the smallest interva=
l=20
unit.  It would be understood that such rounding carries inherent=20
inaccuracies, in the the same way that Float rounding does.  In a "rounded"=
=20
Interval value, all of the following would be true:
1 minute =3D 60 seconds
1 hour =3D 60 minutes
1 day =3D 24 hours
1 week =3D 7 days
1 month =3D 30 days
1 year =3D 365 days =3D 12 months =3D 52 weeks

Thus:
'7 months' / '3 days' =3D 70
... even though this is frequently off by +/- 1 on real calendars.

Of course, this would require a builtin function interval_round(INTERVAL,=
=20
Interval Unit).  Also, the Interval data type would have to be able to hold=
=20
counts of interval units higher than the threshold for the next unit=20
increment, i.e. '345 hours' would have to be a valid INTERVAL on its own=20
without being automatically rounded into days + hours by the system.

Comments, please?

--=20
-Josh Berkus