Jonathan Leffler <jleffler@earthlink.net> wrote on 09/10/2004 08:53:34 AM:
>Jean Sagi <jeansagi@myrealbox.com> wrote:
> >From: Jonathan Leffler <jleffler@earthlink.net>
> >That will give an interval in days, not in months and days.
> >
> >That's because there are two classes of interval -
> >year/month and day/hour/minute/second. Noticably
> >absent is any type of interval that mixes months and
> >days, for the simple reason that different months
> >have a different number of days, notwithstanding the
> >various fictions that the bond market uses.
>
> Ummm... well that's some limitation...

It is a Standard SQL limitation. That is, Standard SQL prescribes these
two distinct classes and insists (legitimately) that they cannot be mixed.
On intervals, Informix hews very close to the standard (the only
significant deviations being in the insistence on TO DAY in INTERVAL
DAY(4) TO DAY and the use of parentheses around literals instead
of quotes).

> I think I'm not going to get the elapsed months and days with just sql.
>
> >require INTERVAL DAY(n) TO DAY as the type -- value,
>
> That is the proper way cast to an interval of days:
>
> select (extend("2003-11-04", YEAR TO DAY) - extend("2003-06-04",
> YEAR TO DAY))::INTERVAL day(3) TO day as elap
> from table( set{1} );
>
> elap
> ----
> 153 -- The _interval_ of days between 2 datetimes
>
> But note something interesting:
>
> select (extend("2003-11-04", YEAR TO DAY) - extend("2003-06-04",
> YEAR TO DAY))::INTERVAL day(2) TO day as elap
> from table( set{1} );
>
> Error: Overflow occurred on a datetime or interval operation.
>
> Correct because the interval is a 3 digit numer of days
>
> select (extend("2003-11-04", YEAR TO DAY) - extend("2003-06-04",
> YEAR TO DAY))::INTERVAL day(4) TO day as elap
> from table( set{1} );
>
> elap
> ----
> 15 -- What this 15 mean? why day(4) gives this?

Which version of IDS? Which program? From SQLCMD and IDS 9.50.UC1(N395)
and 9.40.UC4 and 9.30.UC5 (on Solaris 8) I get the same correct answer
(153)
in each case.

IDS 7.31 does not support the cast notation, but I get the same answer
(153)
from the equivalent statements:

SELECT INTERVAL(0) DAY(3) TO DAY +
(EXTEND("2003-11-04", YEAR TO DAY) - EXTEND("2003-06-04", YEAR TO
DAY))
AS elapsed
FROM dual;

If you're really getting the behaviour you show, then you've got a bug,
but it
is either platform specific or version specific and probably would be
fixed by
an upgrade.

> And finally I understand that there are two types of intervals: _day
> to ...second_ and _year to month_
>
> So How, the above examples, could be casted/coerced to an interval
> of type _year to month_ :
>
> select (extend("2003-11-04", YEAR TO DAY) - extend("2003-06-04",
> YEAR TO DAY))::INTERVAL year TO month as elap
> from table( set{1} );

SELECT EXTEND(EXTEND('2003-11-04', YEAR TO DAY), YEAR TO MONTH) -
EXTEND(EXTEND('2003-06-04', YEAR TO DAY), YEAR TO MONTH) AS elapsed
FROM dual;

Or, the inner EXTEND operations could be replaced by, for example,
DATETIME(2003-11-04) YEAR TO DAY
since it really does represent a datetime literal.

Note that it doesn't matter which day of the month is present in either of
the datetime literals; you get the same answer (0-05) from them all. If
this is not the behaviour you require, you have to do some fancy footwork.
That might be along the lines of subtracting the following from the
expression above:

CASE WHEN (DAY('2003-11-04') < DAY('2003-06-30'))
THEN 1 UNITS MONTH
ELSE 0 UNITS MONTH
END

That plays a bit fast'n'loose with implicit conversions -- to be explicit,
I should have a DATE operator in there (or an EXTEND, or a DATETIME
literal),
and I'm assuming that DBDATE="Y4MD-". Make your adjustments accordingly.

> gives:
>
> !finderr -1266
> -1266 Intervals or Datetimes are incompatible for the operation.

The trouble is that the cast is operating on an INTERVAL DAY(n) TO DAY
value,
and you cannot coerce that into an INTERVAL YEAR TO MONTH -- there is no
way
to breach the Y/M vs D/H/M/S divide. The two classes of intervals are
completely disjoint.

> I thought that at least I could know the number of elapsed months
> with some cast of INTERVAL year TO month , but it would be
> equivalent to something like:
>
> select month( date("2003/11/04") ) - month( date("2003/06/04") )
> from table( set{1} );

Not complete -- you need:

SELECT YEAR(DATE("2003/11/04"))*12+MONTH(DATE("2003/11/04")) -
YEAR(DATE("2000/02/29"))*12+MONTH(DATE("2000/02/29")) AS elapsed
FROM dual;

> ... Maybe some bladelet could do this ...

Yes, it could. It would be overkill - an SPL function would be
adequate - but a bladelet could do it.

sending to informix-list