How to cast to interval?
While reading documentation I ask: How to do a cast to an interval? I mean: select "2003/11/04"::date - "2003/06/04"::date elap from table( set{1} ) This give the number of days elapsed between 2003/11/04 and 2003/06/04, isn't it. It is supposed that the difference of this two dates is some kind of interval. How do I express the same result in terms of month and days? Chucho! Jean Sagi jeansagi@myrealbox.com jeansagi@yahoo.com sending to informix-list
On Thu, 09 Sep 2004 17:57:52 -0400, Jean Sagi wrote: An INTERVAL is the difference between two DATETYPE values, not two dates. That would just result in an integer. You can coerce that result to an INTERVAL or change the query: select extend("2003-11-04", YEAR TO DAY) - extend("2003-06-04", YEAR TO DAY) as elap from table( set{1} ); Art S. Kagel > While reading documentation I ask: > > How to do a cast to an interval? > > I mean: > > select "2003/11/04"::date - "2003/06/04"::date elap from table( set{1} ) > > This give the number of days elapsed between 2003/11/04 and 2003/06/04, > isn't it. It is supposed that the difference of this two dates is some kind > of interval. > > How do I express the same result in terms of month and days? > > > Chucho! > > > > Jean Sagi > jeansagi@myrealbox.com > jeansagi@yahoo.com > > sending to informix-list
Art S. Kagel wrote: > On Thu, 09 Sep 2004 17:57:52 -0400, Jean Sagi wrote: >>While reading documentation I ask: >>How to do a cast to an interval? >>I mean: >> >>select "2003/11/04"::date - "2003/06/04"::date elap >> from table( set{1} ) >> >> This give the number of days elapsed between 2003/11/04 and >> 2003/06/04, isn't it. It is supposed that the difference of this >> two dates is some kind of interval. >>How do I express the same result in terms of month and days? > > An INTERVAL is the difference between two DATETYPE values, not two dates. DATETIME, of course... > That would just result in an integer. You can coerce that result to an > INTERVAL or change the query: > > select extend("2003-11-04", YEAR TO DAY) - extend("2003-06-04", YEAR TO DAY) > as elap > from table( set{1} ); 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. Note that Informix supported a DATE type a long time before the SQL standard did (6 years or more). In standard SQL, the difference between two DATE values would be an INTERVAL DAY(n) -- Informix would require INTERVAL DAY(n) TO DAY as the type -- value, but Informix DATE types map to integers, and the difference between two DATE values is an integer number of days, not an INTERVAL type. Actually, that's much the more useful type in general. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/