Re: Handling INTERVAL datatypes

On Mon, 8 Sep 1997, Peter Jessup wrote: > I have a table that contains a couple of columns defined as INTERVAL > HOUR TO SECOND. Is there an easy way using SQL to cast this into an > INTEGER datatype representing the number of seconds in the interval? Well, you need to be able to control the interval type, and AFAIK the only way of doing that in pre-IUS systems is via a stored procedure such as: CREATE PROCEDURE iv_seconds(iv INTERVAL DAY(5) TO FRACTION(5)) RETURNING INTERVAL SECOND(9) TO FRACTION(5); DEFINE rv INTERVAL SECOND(9) TO FRACTION(5); LET rv = iv - 0 UNITS SECOND; RETURN rv; END PROCEDURE; > Failing that can I retrieve the number of hours, minutes and seconds > into separate integers? No. If they were DATETIME HOUR TO SECOND, you'd be able to use: LET hh = EXTEND(iv, HOUR TO HOUR) LET mm = EXTEND(iv, MINUTE TO MINUTE) LET ss = EXTEND(iv, SECOND TO SECOND) Or, in SQL: SELECT EXTEND(iv, HOUR TO HOUR) AS hh, EXTEND(iv, MINUTE TO MINUTE) AS mm, EXTEND(iv, SECOND TO SECOND) AS ss, ... However, there doesn't seem to be a documented function equivalent to EXTEND for changing the characteristics of INTERVAL data types. Yours, Jonathan Leffler (johnl@informix.com) #include <witticism.h>