Oliver,
> You are proposing a mathematical operation that looks exact but
> actually
> has a fuzzy result and the reason it is fuzzy is that you are trying
> to
> shortcut the process of deciding what rule to apply.
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
allof 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.
>You gave the example
>
> interval_round('7 months','days') / '3 days' = 70
>
> which seems to assume that a month is 30 days. On a different basis
> the
> result would be 365.2425 * 7 / 12 / 3 = 71.019375 or 365 * 7 / 12 /
> 3
> = 70.972222. Both of those round to 71 days. Your 7 months interval
> is
> anything from 209 to 213 days depending on which months it includes,
> which is unknowable without a context.
Absolutely.
> 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) ?
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?
-Josh Berkus