A proposal for Interval Math - Mailing list pgsql-sql

Developers:

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

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

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

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

Thus:
INTERVAL / INTEGER = INTERVAL + ( INTERVAL % INTEGER )
INTERVAL * INTEGER = INTERVAL
INTERVAL / INTERVAL = INTEGER + ( INTERVAL % INTERVAL )

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

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

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

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

Comments, please?

--
-Josh Berkus