Re: A proposal for Interval Math

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 users
need to, for example, calculate the number of 2-week intervals in a given
interval column all the time. Right now, everybody is using the Date data
type and integers, which has some significant shortcomings.

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

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

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

--
-Josh Berkus