need more TIME functions

Hi, I am using IDS 10.0 and i had been working on SPL routines which work with DATETIMEs and INTERVALs, and from my experience they are pretty inflexible and IDS should have provided more functions for these types. 1) When you subtract two DATETIMES you get back an INTERVAL type, but this INTERVAL cant be converted into say.. all seconds.. or some thing like that. In my case, a table has a DATETIME column in which i put the timestamps of certain events. I simply dont have a way to calculate the fraction/percentage of time spent during events. ie ; event 1 : timestamp event 2 : timestamp event n : timestamp I tried a lot, but there is no way in SPL routines for me to calculate the fraction of time spent between event1 and event2 as compared to the total time (event n - event 1) INTERVAL types need to support "Divide" operation. or alteast IDS has to have functions that work with INTERVALS just like EXTEND function for DATETIME conversions 2) Timezone : I searched a lot but couldnt find functions that can convert a DATETIME from one time zone to other, taking care of these day light savings times. The user has to supply his own INTERVAL to subtract from the DATETIME. It would be nice to have it in the DB itself rather than at some client application level. thanks, Prasad.

As for getting intervals that you can work with, you should be able, in 9.xx+, to cast the result of the difference in datetimes to an interval second(9) to second: LET diff = (event2 - event1) :: INTERVAL SECOND(9) TO SECOND; That should give you diff as a single number. Now, here's the tricky part, you cannot perform math on intervals, as you've discovered, and you cannot assign an interval to a numeric type, but you CAN assign the interval to a CHAR type containing the number of seconds then assign the string to a numeric type for calculations: DEFINE diff interval second(9) to second; DEFINE diffstr character(10); DEFINE diffnum integer; LET diff = (event2 - event1) :: INTERVAL SECOND(9) TO SECOND; LET diffstr = diff LET diffnum = diffstr As to the rest, I agree Informix didn't think through all of the remifications of the date and datetime types. You could build your own UDF functions in a databladelet to perform all of the conversions even to define a new datetimewithtz type that automatically takes timezone into account using the UDFs you've built. Then you don't need user level functions except to convert from this type to UNIX time types (of course your new type could just BE a UNIX time type like struct tm!) It's really not that hard. Art ----- Original Message ----- From: Prasad Velagaleti <ids@iiug.org> To: ids@iiug.org At: 9/18 16:01:25 Hi, I am using IDS 10.0 and i had been working on SPL routines which work with DATETIMEs and INTERVALs, and from my experience they are pretty inflexible and IDS should have provided more functions for these types. 1) When you subtract two DATETIMES you get back an INTERVAL type, but this INTERVAL cant be converted into say.. all seconds.. or some thing like that. In my case, a table has a DATETIME column in which i put the timestamps of certain events. I simply dont have a way to calculate the fraction/percentage of time spent during events. ie ; event 1 : timestamp event 2 : timestamp event n : timestamp I tried a lot, but there is no way in SPL routines for me to calculate the fraction of time spent between event1 and event2 as compared to the total time (event n - event 1) INTERVAL types need to support "Divide" operation. or alteast IDS has to have functions that work with INTERVALS just like EXTEND function for DATETIME conversions 2) Timezone : I searched a lot but couldnt find functions that can convert a DATETIME from one time zone to other, taking care of these day light savings times. The user has to supply his own INTERVAL to subtract from the DATETIME. It would be nice to have it in the DB itself rather than at some client application level. thanks, Prasad. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Thats a nice trick. thanks Art.

Also please note that Version 11 has added many nice date functions, so= me of them are: ADD_MONTHS MONTHS_BETWEEN NEXT_DAY LAST_DAY ROUND(), TRUNC() SYSDATE John = "PRASAD = VELAGALETI" = <vdprasad@hotmail = To .com> ids@iiug.org = Sent by: = cc ids-bounces@iiug. = org Subj= ect Re:need more TIME functions [99= 70] = 09/18/2007 04:51 = PM = = = Please respond to = ids@iiug.org = = = Thats a nice trick. thanks Art. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

You should find a bunch of stored procedures doing this sort of stuff at the IIUG web site in the software archive. There are also some C code functions (using ESQL/C library) that convert intervals in different ways - such as converting an INTERVAL DAY(9) TO FRACTION(3) into a decimal number representing the total seconds. The ratio of two intervals is on my private 'to do' list, along with time zones. On 9/18/07, ART KAGEL, BLOOMBERG/ 731 LEXIN <kagel@bloomberg.net> wrote: > As for getting intervals that you can work with, you should be able, in 9.xx+, > to cast the result of the difference in datetimes to an interval second(9) to > second: > > LET diff = (event2 - event1) :: INTERVAL SECOND(9) TO SECOND; > > That should give you diff as a single number. Now, here's the tricky part, you > cannot perform math on intervals, as you've discovered, and you cannot assign > an interval to a numeric type, but you CAN assign the interval to a CHAR type > containing the number of seconds then assign the string to a numeric type for > calculations: > > DEFINE diff interval second(9) to second; > > DEFINE diffstr character(10); > > DEFINE diffnum integer; > > LET diff = (event2 - event1) :: INTERVAL SECOND(9) TO SECOND; > > LET diffstr = diff > > LET diffnum = diffstr > > As to the rest, I agree Informix didn't think through all of the remifications > of the date and datetime types. You could build your own UDF functions in a > databladelet to perform all of the conversions even to define a new > datetimewithtz type that automatically takes timezone into account using the > UDFs you've built. Then you don't need user level functions except to convert > from this type to UNIX time types (of course your new type could just BE a > UNIX > time type like struct tm!) It's really not that hard. > > Art > > ----- Original Message ----- > From: Prasad Velagaleti <ids@iiug.org> > To: ids@iiug.org > At: 9/18 16:01:25 > > Hi, > > I am using IDS 10.0 and i had been working on SPL routines which work with > DATETIMEs and INTERVALs, and from my experience they are pretty inflexible and > IDS should have provided more functions for these types. > > 1) When you subtract two DATETIMES you get back an INTERVAL type, but this > INTERVAL cant be converted into say.. all seconds.. or some thing like that. > > In my case, a table has a DATETIME column in which i put the timestamps of > certain events. I simply dont have a way to calculate the fraction/percentage > of time spent during events. > ie ; > event 1 : timestamp > event 2 : timestamp > > event n : timestamp > > I tried a lot, but there is no way in SPL routines for me to calculate the > fraction of time spent between event1 and event2 as compared to the total time > (event n - event 1) > > INTERVAL types need to support "Divide" operation. or alteast IDS has to have > functions that work with INTERVALS just like EXTEND function for DATETIME > conversions > > 2) Timezone : I searched a lot but couldnt find functions that can convert a > DATETIME from one time zone to other, taking care of these day light savings > times. > > The user has to supply his own INTERVAL to subtract from the DATETIME. It > would be nice to have it in the DB itself rather than at some client > application level. > > thanks, > Prasad. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0826 -- http://dbi.perl.org/ NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.