One more question about conversions.

Hi! How can I convert datetime hour to second to integer amount of seconds from 00:00 ? Also, how can i do these procedures: _hour(datetime ...), _second(datetime ...), minute_(datetime) ? -- Regards, Alex Antropoff

Alex Antropoff wrote: > How can I convert datetime hour to second to integer amount of > seconds from 00:00 ? Also, how can i do these procedures: > _hour(datetime ...), _second(datetime ...), minute_(datetime) ? Dave, Is this material in the FAQ list, because minor variants on this stuff has been a major FAQ in the last year or so. Alex, You can use: INTERVAL(0) SECOND(9) TO SECOND + (dt - DATETIME(00:00:00) HOUR TO SECOND) Where dt is your DATETIME HOUR TO SECOND variable. The subtraction converts from a DATETIME to an INTERVAL, and the addition ensures that the result is an INTERVAL in seconds. Beware that it is not documented anywhere that the result of adding two intervals takes on the type of the first operand, but that seems to work at the moment. If you need to coerce this to an integer, then you have a variety of choices. I gather one of them has collected my name... Art S. Kagel <kagel@bloomberg.net> wrote: >Alex Antropoff wrote: >> How can I do [conversion between integer and interval] > >Perform the "Leffler Interval Conversion Manuever". First convert >the Interval to a string containing the number of seconds by simply >assigning the interval to a char(6) or longer variable then convert >the numberic string to an integer by simply assigning the char >variable to an integer variable. > >Art S. Kagel > >Aside: >There, Jonathan, now I'm not the only one with some Informix >trick named after me! So you can use the LICK-ME (not a trade mark!) technique in a stored procdure, or you can do it inline if you really have to: ((INTERVAL(0) SECOND(9) TO SECOND + (dt - DATETIME(00:00:00) HOUR TO SECOND)) || "") + 0 It is neater in a stored procedure; you declare the SP as returning an INTEGER, but you assign the expression with addition and subtraction to a string and simply return the string. Informix implicitly takes care of the conversions. You can also go to the archives (either at the IIUG web site, http://www.iiug.org, or at DejaNews, http://www.deja.com) and dig out the old discussions on the subject. You should even find some ESQL/C or C code from me which explicitly returns mixed intervals into selected other units; it returns decimals rather than integers so that if you pass in an INTERVAL HOUR TO SECOND and ask for hours, you get back an appropriate fractional value. It also works around little issues such as the fact the you cannot have more than 10**9 seconds in an interval. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>

In article <37DACBD1.6391@earthlink.net>, Jonathan Leffler <jleffler@earthlink.net> writes >Alex Antropoff wrote: >> How can I convert datetime hour to second to integer amount of >> seconds from 00:00 ? Also, how can i do these procedures: >> _hour(datetime ...), _second(datetime ...), minute_(datetime) ? > >Dave, > >Is this material in the FAQ list, because minor variants on this >stuff has been a major FAQ in the last year or so. > Ok, I'll add it tonight.. -- David Williams