Re: A proposal for Interval Math - Mailing list pgsql-sql

On Mon, 2002-05-20 at 17:03, Josh Berkus wrote:
> Oliver,
> But it is a fuzzy process.  Three months is not the same number of days
> from January-March as it is from June-August.    For that matter, March
> 7th 2002 has 25 hours in California, but only 24 hours in Arizona.
>  While an ideal database would allow accounting for all of these rules,
> it is practically impossible.
>
> > You mentioned calculating the number of 2-week intervals in an
> > interval
> > column: if you need to do this, the column should not be defined as
> > YEAR
> > TO MONTH but as DAY (of course, PostgreSQL hasn't yet implemented
> > these
> > subtypes of INTERVAL).  If you *have* to do it with a YEAR TO MONTH
> > column, you have implicitly defined a local rule that averages out
> > the
> > difference in month lengths, because in a YEAR-MONTH interval, you
> > don't
> > know how long a year or a month is.
>
> Precisely.  You have to estimate.  And the database system should allow
> for either implicit or explicit estimation.

I don't think I am convinced that this needs to be provided by the
database.  If you say that your application just has to divide frogs by
oranges (naturally the result is going to be somewhat fuzzy) I might
request further justification!  I think I am right in being suspicious
of any operation on fuzzy data that purports to give a firm result.  Why
do we have to do it this way? doesn't the fact that you want to do this
demonstrate that there is something wrong with the database design or
with the business rules?

...
> > So if you define interval_round() you must also include some means of
> > configuring what rule to use, because that is not necessarily going
> > to
> > be the same in every organisation.  It might not even be the same
> > between different applications in one organisation.
>
> This makes sense but it would be harder to implement.  I suppose it all
> depends on what programmmers have time, yes?  I take it you're
> proposing:
> interval_round(interval_value, interval_unit, interval_rule)  ?

Or a SETtable parameter -- IF we go so far as to do this!

However, since I see we can do this:

junk=# select cast('7 months' as interval) / 3;                            ?column?
----------------2 mons 10 days
(1 row)

my objections are beside the point and we might as well do it..

> Given that you seem to have given this some thought as well, I'd like
> to hear your ideas on a workable solution for interval math.  Also, I
> notice that you did not comment on my proposal that we allow interval
> multiplication only by integers.  Does that mean you agree?

INTERVAL * {INTEGER|FLOAT|NUMERIC} would be meaningful; why restrict it
to integer?  For example, one might very well want INTERVAL * 1.5.  The
result of any of these would be INTERVAL of the same subtype as the
multiplied interval.  I agree that it doesn't make much sense to
multiply intervals together.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
    "For all that is in the world, the lust of the flesh,      and the lust of the eyes, and the pride of life, is
notof the Father, but is of the world."                                   I John 2:17