Hi Herb,

<< If the widely used db's like SQL Server, Oracle, MySQL, PostgreSQL, et
al. have a standard way of treating these intervals then that seems like the
obvious correct choice to implement in ElevateDB SQL. >>

I agree. However, I don't think there are many RDBMS having a complete
implementation of intervals/interval arithmetic like EDB. SQL Server has a
proprietary DATEDIFF function, Oracle only supports multi-field intervals
like YEAR TO MONTH etc. AFAIK, MySQL has an INTERVAL() function with integer
parameters. The only RDBMS I know of with a complete, standard
implementation of intervals besides EDB is Mimer SQL. EDB is therefore
pretty strong in this department

Regards,
Ole

Tim,

<< If you ask for a year-month interval, then it converts the dates to
months (the smallest uom for year-month intervals), and does the
calculation. >>

Yes. but what algorithm is used for calculating the year-month or
specifically the month interval ? Does the calculation take the full date
expressions into account, or does it disregard the day fraction of the date
values ?

Let's play with the following year-month expression:

(date'2007-04-01'-date'2000-04-30') month

EDB returns the value 84, which translates to 7 years - cast(interval'84'
month as interval year).

If we cast the date values to integers using truncation (to avoid the time
portion), the following algorithms:

a) Julian calendar

((floor(cast(date'2007-04-01' as double
precision))-floor(cast(date'2000-04-30' as double precision)))/365.25)*12

b) Gregorian calendar

((floor(cast(date'2007-04-01' as double
precision))-floor(cast(date'2000-04-30' as double precision)))/365.2425)*12

both return the integer value 83 (both with rounding or truncation), which
translates to 6 years - cast(interval'83' month as interval year).

This is what confuses me, and make me wonder if the EDB calculation of the
year-month interval between two dates is correct.

Ole Willy Tuv

"Ole Willy Tuv" <owtuv@online.no> wrote in message
news:3CA96965-C374-40D9-8799-0CCC8898CDA6@news.elevatesoft.com...
> Hi Herb,
>
> << If the widely used db's like SQL Server, Oracle, MySQL, PostgreSQL, et
> al. have a standard way of treating these intervals then that seems like
the
> obvious correct choice to implement in ElevateDB SQL. >>
>
> I agree. However, I don't think there are many RDBMS having a complete
> implementation of intervals/interval arithmetic like EDB. SQL Server has a
> proprietary DATEDIFF function, Oracle only supports multi-field intervals
> like YEAR TO MONTH etc. AFAIK, MySQL has an INTERVAL() function with
integer
> parameters. The only RDBMS I know of with a complete, standard
> implementation of intervals besides EDB is Mimer SQL. EDB is therefore
> pretty strong in this department
>
> Regards,
> Ole
>

Ole -- You're right, of course.  I was thinking of functions like DATEDIFF
in SQL Server.  If I recall correct that one operates always a sort of
interval "MOD" function.  That is, whatever interval you're using, the
function returns a value analogous to an arithmetic mod function on the
difference in time.  E.g., if two dates are 10 months, 3 weeks, and 2 days
apart a YEAR datediff returns 0, MONTH datediff returns 10, week and day
similarly.  That's the only that makes sense to me.  I don't see any other
way to have all the different interval operations consistent with each
other.

-- Herb

Herbert


I like that one. I can understand it

Roy Lambert

Ole,

<< Yeah, but is the result correct - that's the question ? >>

"Correct" is such a misleading term.

I would say no, the answer is not correct in the sense that it would be what
someone expects, especially if they think that the day (or smaller units of
time like the time portion) should be taken into account, like you did.
The issue, however, is do we break the rule that year-month intervals take
into account date/time units that are normally reserved for day-time
intervals.   Frankly, I don't know, so I'll have to consult the standard
docs and see what I can find.  All I found about this during development was
"don't mix the two interval types".   However, I think that there's a
section on what the results of interval calculations should be, and perhaps
a footnote somewhere will mention this issue.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ole,

<< More spesifically - would you assume that the year interval between
DATE'2007-04-18' and DATE'2000-04-19' is 6 or 7 ? I would say 6 is the
correct value. >>

And I wouldn't say that you were wrong.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ole,

<< Yes. but what algorithm is used for calculating the year-month or
specifically the month interval ? Does the calculation take the full date
expressions into account, or does it disregard the day fraction of the date
values ? >>

It disregards the day for the simple fact that I was under the impression
that we were not allowed to take into account anything less than a month
when calcuating year-month intervals.

--
Tim Young
Elevate Software
www.elevatesoft.com