Tim,

select
 cast((timestamp'2007-04-18 00:00:00'-timestamp'2000-04-19 00:00:00') year
as integer) year_1,
 cast((timestamp'2007-04-18 00:00:00.000'-timestamp'2000-04-18
00:00:00.001') year as integer) year_2
from dummy

1.02 b1 returns the value 7 for both expressions. I'd expect the correct
result for both expressions to be 6.

Ole Willy Tuv

Ole,

<< 1.02 b1 returns the value 7 for both expressions. I'd expect the correct
result for both expressions to be 6. >>

YEAR intervals cannot take into account time values (YEAR-MONTH <>
DAY-TIME).  EDB simply grabs the date from each timestamp value and
calculates the year difference.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< YEAR intervals cannot take into account time values (YEAR-MONTH <>
DAY-TIME).  EDB simply grabs the date from each timestamp value and
calculates the year difference. >>

Yes, but this is an intervalue expression based on two datetime values,
which can be any interval type depending on the interval qualifier. I
thought that the time field would be taken into account in the internal
algorithm when the result was finally converted to a YEAR interval.

In any case, also the ((timestamp'2007-04-18 00:00:00'-timestamp'2000-04-19
00:00:00') year expression returns 7.

Ole Willy Tuv

Ole,

<< Yes, but this is an intervalue expression based on two datetime values,
which can be any interval type depending on the interval qualifier. I
thought that the time field would be taken into account in the internal
algorithm when the result was finally converted to a YEAR interval. >>

This issue is very similar to Richard's issue with calculating the age in
years - the issue is how many days are in a year, or how many days in a year
should EDB use for converting days into years ?  Right now EDB does not mix
day-time intervals with year-month intervals in any way.  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.  That way you get the
same result regardless of whether you're subtracting dates or timestamps -
the calculation is done only on the date portion of the value.

--
Tim Young
Elevate Software
www.elevatesoft.com

<< Right now EDB does not mixday-time intervals with year-month intervals in
any way.  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.  That way you get the same result regardless of whether you're
subtracting dates or imestamps - the calculation is done only on the date
portion of the value. >>

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

Ole Willy Tuv

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

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.

Ole Willy Tuv

Ole


How's about thinking of it in terms of when someone asks you your age? In my case (DoB 7/10/1951) the answer would be either 56 or 56 in October so I'll go with Tim's approach.

Roy Lambert

Roy Lambert wrote:

> Ole
>
>
> How's about thinking of it in terms of when someone asks you your age? In my
> case (DoB 7/10/1951) the answer would be either 56 or 56 in October so I'll
> go with Tim's approach.
>

Surely it must be the empirical 56.  Otherwise I am older than I feel.
Birthday in April '51.

Ian



--

Roy,

<< How's about thinking of it in terms of when someone asks you your age? In
my case (DoB 7/10/1951) the answer would be either 56 or 56 in October so
I'll go with Tim's approach. >>

The approach is not like that

AFAICS, the day part of the dates are disregarded in the calculation of a
year-month interval:

(current_date-date'1951-10-07') year returns 55
(date'2007-09-30'-date'1951-10-07') year returns 55
(date'2007-10-01'-date'1951-10-07') year returns 56
(date'2007-10-01'-date'1951-10-31') year returns 56

I'm not sure whether this approach is correct or incorrect in terms of
interval theory, which is why I thought it would be worth a discussion.

I've checked the examples with Mimer SQL, and Mimer returns 55 with all the
expressions. This is the result I personally find logical, but it might well
be that the EDB results are correct as well. I simply don't know

Ole Willy Tuv

"Ole Willy Tuv" <owtuv@online.no> wrote in message
news:E9E10E83-1AC4-4C43-BEBF-21A536918CFE@news.elevatesoft.com...
>
> I've checked the examples with Mimer SQL, and Mimer returns 55 with all
the
> expressions. This is the result I personally find logical, but it might
well
> be that the EDB results are correct as well. I simply don't know
>
> Ole Willy Tuv
>

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.  What seems "logical"
may differ for different people, but if there's agreement between the big
db's there's not much use going against the grain, imo.

-- Herb