Re: Retrieve 3o minutes [395]

Sorry, I always get the INTERVAL constants wrong, it should be: select ... from mytable where ..... and insert_time BETWEEN (CURRENT - INTERVAL (30) minute(2) to minute) and CURRENT.... ; -OR I could have used the UNITS as you did: select ... from mytable where ..... and insert_time BETWEEN (CURRENT - (30) UNITS minute) and CURRENT.... ; But both solutions require that the time column, here insert_time, is of resolution DATETIME YEAR to MINUTE or finer. Some part of your problem is because you have the date and time in separate columns. That gives you problems when the subtraction of the time moves you to an earlier day. In addition, you want to report the total number of rows (count(*)) within each 30 minute period, that's a whole different ball of wax and you can't do that simply in SQL. To fix that is difficult, but let's try. I'm going to use a stored procedure to do all of the work because working with datetimes and char values inline is awkward at best. Another solution would be to call a stored procedure/function from the projection clause to return the time interval values for a given time (say return 00:00 from 00:00 to 00:29 then 00:30 from 00:30 to 00:59, etc) including just the day, function return, and a count(*) in the projection clause and use a GROUP BY clause on the day and interval. create function summarize_by_time( start_day date, start_time datetime hour to hour ) returning datetime year to day as row_date, datetime hour to minute as starttime, int as calls; define day datetime year to day, time datetime hour to minute; define intvl, now, compare datetime year to second; int counter; LET counter = 0; LET now = (start_day || ' ' || start_time || ':30') datetime year to minute; LET intvl = (start_day || ' ' || start_time) datetime year to minute; FOREACH SELECT row_date, starttime FROM hvdn WHERE row_date > start_day AND starttime > (start_time::DATETIME HOURTO MINUTE); LET compare = (row_date || ' ' || starttime); IF now <= compare THEN RETURN intvl::DATETIME YEAR TO DAY, intvl::DATETIME HOUR TO MINUTE, counter WITH RESUME; LET counter = 0; LET compare = compare + (30) UNITS MINUTES; LET intvl = intvl + (30) UNITS MINUTES; ELSE LET counter = counter + 1; END IF; END FOREACH; RETURN intvl::DATETIME YEAR TO DAY, intvl::DATETIME HOUR TO MINUTE, counter; end function; EXECUTE FUNCTION summarize_by_time( current year to day, (current hour tominute - (55) units minute) ); Note that the return value naming will only work in IDS 11.10 and later. Art On Sat, Dec 27, 2008 at 4:43 PM, Ravi Rasu <ravi.rasu@gmail.com> wrote: > select * from HVDN where hvdn.row_date = today and hvdn.starttime > > REPLACE((current hour to minute - 55 units minute) - today,":","")> > > Thank you for your email. > when i use (CURRENT - (30) INTERVAL minute(2) to minute) i am getting > syntac error. > > for ex table values are like this > > row_date starttime calls > 12/27/2008 0 87 > 12/27/2008 30 8 > 12/27/2008 100 889 > .... > ... > 12/27/2008 2330 99 > > the final 2330 values comes at 12/28/2008 after 12 AM .. > > I have to query these records > for ex if i subtract 45 minutes or so .how do i round the value to 30 > minutes interval.. > > for ex 1142 to 1130 .. > 1210 to 1200 > > Please let me know your thoughts > > Thank you for your help > > > > > On Fri, Dec 26, 2008 at 3:22 PM, Art Kagel <art.kagel@gmail.com> wrote: > >> Assuming the table has a DATETIME YEAR TO MINUTE or higher resolution >> column >> in it (assume that column is called insert_time): >> >> select ... >> from mytable >> where .....>> >> and insert_time between (CURRENT - (30) INTERVAL minute(2) to minute) >> and CURRENT >> .... >> ; >> >> Art >> >> On Fri, Dec 26, 2008 at 12:55 PM, ROB RAS <ravi.rasu@gmail.com> wrote: >> >> > How to retreive last 30 minutes data from informix? >> > for ex interval format will be 1100 ,1130,1200,1230 ,1300 and 1330 for >> > every >> > half hour. >> > >> > >> > >> > >> >> ******************************************************************************* >> > Forum Note: Use "Reply" to post a response in the discussion forum. >> > >> > >> >> -- >> Art S. Kagel >> Oninit (www.oninit.com) >> IIUG Board of Directors (art@iiug.org) >> >> Disclaimer: Please keep in mind that my own opinions are my own opinions >> and >> do not reflect on my employer, Oninit, the IIUG, nor any other >> organization >> with which I am associated either explicitly or implicitly. Neither do >> those opinions reflect those of other individuals affiliated with any >> entity >> with which I am affiliated nor those of the entities themselves. >> >> >> >> ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.