A proposal for Interval Math

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