Skip site navigation (1)
Skip section navigation (2)
## Re: A proposal for Interval Math

### In response to

### Responses

### pgsql-sql by date

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 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. >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

- Re: A proposal for Interval Math at 2002-05-20 07:54:51 from Oliver Elphick

- Re: A proposal for Interval Math at 2002-05-20 18:35:37 from Oliver Elphick

Next: From:Jean-Luc LachanceDate:2002-05-20 16:07:37Subject: Re: count(boolean)Previous: From: Masaru SugawaraDate: 2002-05-20 15:31:05Subject: Re: Using LASTOID in one query