Re[2]: How to format an interval data type?

Jonathan Thanks much. Created a SP and now things are fine. Tried few other things like creating another column INTERVAL MINUTE(9) TO MINUTE and updating with CURRENT - event_since, but could not do any arithmetic with interval and integer when it goes fractional. Ultimately went with the SP approach. Thanks again. Sujit Pal ______________________________ Reply Separator _________________________________ Subject: RE: How to format an interval data type? Author: "Leffler; Jonathan" <jleffler@visa.com> at Internet Date: 5/4/98 2:35 PM There isn't a straight-forward way to do it, but it can be done. Basically, if you want to control the result of INTERVAL arithmetic, you have to do it in a stored procedure where you can control what goes on. You could use something like: -- This code not formally tested (only a transcription of it, but that worked)... CREATE PROCEDURE iv_minutes(iv INTERVAL DAY(5) TO MINUTE) RETURNINGDECIMAL; DEFINE i1 INTERVAL MINUTE(9) TO MINUTE; DEFINE st CHAR(15); LET i1 = iv - 0 UNITS MINUTE; -- Maybe a simple assignment will work too... -- Also note that LET st = i1; -- Explicit conversion to string RETURN st; -- Implicit conversion to DECIMAL END PROCEDURE; The DAY(5) TO MINUTE value is chosen so that INTERVAL(99999 23:59) DAY(5) TO MINUTE can be converted safely to MINUTE(9) TO MINUTE. If the 5 is increased to 6, then some intervals cannot be converted. If you need to handle arbitrary intervals of DAY(9) TO MINUTE, then the conversion process has to be more complex, but it can be done if necessary. To apply this to your example, you'd now write: SELECT iv_minutes(CURRENT - event_since)/cum_num_events events_per_minute FROM event_tracker_table WHERE <condition>; Note that this version of the code loses the information about any seconds or fractions which were originally present in the interval. The same basic technique can be used in numerous other DATETIME and INTERVAL calculation problems. The really hard part is making a small, sufficient set of such stored procedures. If you are working in ESQL/C, I posted some routines to do these conversions in May 1997 - they're in the IIUG archive under the ESQL/C section as intvl_conv. You could adapt the code there to do what you need. These functions preserve the information about fractional minutes, unlike the SP above, and also handle YEAR/MONTH intervals. I also posted some similar code last week when someone was asking about converting from Unix time (seconds since 1970-01-01 00:00:00 UTC) to DATETIME and back again (but as jleffler@earthlink.net, not as jleffler@visa.com). Yours, Jonathan Leffler (jleffler@visa.com) #include <bother.ms-exchange.h> ---------- From: Sujit.Pal@alltel.com[SMTP:Sujit.Pal@alltel.com] Sent: Monday, May 04, 1998 10:36 AM To: informix-list@iiug.org Subject: How to format an interval data type? Hello All Maybe this is really simple, but I have RTFM'd and am still not any wiser. I have a DATETIME column (call it event_since) that captures cumulative point in time statistics in another column in the same table (call it cum_num_events). So I have this query: SELECT (CURRENT - event_since)/cum_num_events events_per_minute FROM event_tracker_table WHERE <condition>; My problem is that (CURRENT - event_since) comes out like 17 03:13:45.000 which is 17 days 3 hours 13 minutes 45.000 seconds. I want to get this in minutes. Is there a way? Thanks Sujit Pal