Re: Datetime and Interval

Tommy We tried to do the very thing you want and finally came up with a workaround that gives the hours between two DATETIME YEAR TO FRACTION columns. The SQL is: SELECT INTERVAL(0) HOUR TO HOUR + (data2 - data1) FROM tablename To get the number of minutes just multiply by 60. I know its not clean but thats what we found. HTH Sujit TOMMY <TOMROME@IOL.IT> on 03/18/99 02:57:36 AM Please respond to TOMMY <TOMROME@IOL.IT> To: informix-list@iiug.org cc: (bcc: Sujit Pal) Subject: Datetime and Interval Jonathan Leffler wrote: > Peter Howe wrote: > > Just as an aside... > > > > Why does Informix use a DATETIME type instead of the the > > SQL TIMESTAMP type? > > AFAIK, Informix implemented a draft version of the SQL-92 standard > DATETIME handling, and the draft changed before the standard was > finalized, but Informix had released the product. > > TIMESTAMP corresponds to DATETIME YEAR TO SECOND. Tanks Jonathan,Peter but I've the problem with a DB that now running for an application and my application (taking information from the DB) need know the number of minutes that was spent from DATA1 to DATA2 two DATETIME YEAR TO FRACTION My problem is it's possible that the difference from two DATE MUST BE AN INTERVALL and not an integer ???? Can I resolve this problem with SPL ?? Massimiliano Panella Tank You

Sujit.Pal@BankAmerica.com wrote: > We tried to do the very thing you want and finally came up with a > workaround that gives the hours between two DATETIME YEAR TO FRACTION > columns. The SQL is: > > SELECT INTERVAL(0) HOUR TO HOUR + (data2 - data1) > FROM tablename > > To get the number of minutes just multiply by 60. I know its not > clean but thats what we found. Interesting. What about: SELECT INTERVAL(0) MINUTE(9) TO MINUTE + (data2 - data1) ... If that works, it saves writing the stored procedure which forces the result type of the interval: CREATE PROCEDURE dt_diff_minutes(d1 DATETIME YEAR TO MINUTE, d2 DATETIME YEAR TO MINUTE) RETURNING INTEGER; DEFINE i INTERVAL MINUTE(9) TO MINUTE; DEFINE s CHAR(10); LET i = d2 - d1; LET s = i; RETURN s; END PROCEDURE; Yes, the string intermediate variable is necessary. > > Jonathan Leffler wrote: > > > Peter Howe wrote: > > > Just as an aside... > > > > > > Why does Informix use a DATETIME type instead of the the > > > SQL TIMESTAMP type? > > > > AFAIK, Informix implemented a draft version of the SQL-92 standard > > DATETIME handling, and the draft changed before the standard was > > finalized, but Informix had released the product. > > > > TIMESTAMP corresponds to DATETIME YEAR TO SECOND. > > Tanks Jonathan,Peter but I've the problem with a DB that now running for an > application and my application (taking information from the DB) need know > the number of minutes > that was spent from DATA1 to DATA2 two DATETIME YEAR TO FRACTION > My problem is it's possible that the difference from two DATE MUST BE AN > INTERVALL > and not an integer ???? > Can I resolve this problem with SPL ?? > > Massimiliano Panella > Tank You -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>