Re: Re: How to cast to interval?

>-----Original Message----- >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... 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? 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} ); gives: !finderr -1266 -1266 Intervals or Datetimes are incompatible for the operation. Some arithmetic combinations of DATETIME, INTERVAL, and numeric values are meaningless and are not allowed. Review the arithmetic expressions in this statement. Possibly one of them is using a DATETIME or INTERVAL column or variable by mistake. If not, see your SQL reference material for the valid use of these data types. 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} ); ... Maybe some bladelet could do this ... Chucho! -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ Jean Sagi jeansagi@myrealbox.com jeansagi@yahoo.com sending to informix-list

Jean Sagi 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... 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? I got a result of 153 - the same as the first test. It might be worth another try on your side. > 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} ); > > gives: > > !finderr -1266 > -1266 Intervals or Datetimes are incompatible for the operation. > > Some arithmetic combinations of DATETIME, INTERVAL, and numeric values are meaningless and are not allowed. Review the arithmetic expressions in this statement. Possibly one of them is using a DATETIME or INTERVAL column or variable by mistake. If not, see your SQL reference material for the valid use of these data types. > > 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} ); > > ... Maybe some bladelet could do this ... There is a hint in the description of the -1266 error that is very helpful. The Informix Guide to SQL: Reference does goes into some detail on this. It says if you need YEAR TO MONTH precision, use the EXTEND function on the first DATE operand. I ran the following on one of my systems: SELECT (EXTEND(DATE("11-04-2003"), YEAR TO MONTH) - DATE("06-04-2003")) AS elap FROM table( set{1} ); and got the following results: elap ------- 0-05 Does that help you? (Oh, and I had to re-order the date for my system, but that shouldn't make any difference as to the end results.) -- June Hunt