englbrechtful Posted October 7, 2009 Share Posted October 7, 2009 Hello Community,situation:Ive got a DB with a table "logging" that has the following relevant columns: time(timestamp), errorcode(string).I want a chart where i can see how often a certain errorcode appears in relation to the time.I tried this with a simple query "select * from logging where time between '2009-09-09' and current_timestamp" for example. then created a time chart with this data. this works.The problem is the big amount of data, there are about 5000 such entrys every day.So I figured i need some pre processing of the data, for example a lot of querys like this:"Select count(*) from logging where errorcode = '1x0' and time between '2009-09-09 00:00:00' and '2009-09-09 00:05:00'"That means if i want to see a graph for a certain error from 24 hours, i need 12*24=288 querys and those results i could use for my chart.Any suggestions how i could implement that?Im happy for every comment! If you dont understand my situation ask, i will try to concretise it.Thx,Stefan Post Edited by englbrechtful at 10/07/2009 11:04 Link to comment Share on other sites More sharing options...
lucianc Posted October 8, 2009 Share Posted October 8, 2009 You can use a single query and do the grouping/aggregation in the report.What you need to do is to sort the results by time, create a report groups that breaks every 5 minutes and then use the count variable for the group in the chart.HTH,Lucian Link to comment Share on other sites More sharing options...
englbrechtful Posted October 9, 2009 Author Share Posted October 9, 2009 Hi Lucian,thanks for your help. I understand your approach but can you please specify :"create a report groups that breaks every 5 minutes" how to do that in ireport? ( i use v3.5.0 ) Link to comment Share on other sites More sharing options...
englbrechtful Posted October 16, 2009 Author Share Posted October 16, 2009 Please Lucian! I dont know how to create a "breaker" that breaks every 5 Minutes :( Link to comment Share on other sites More sharing options...
lucianc Posted November 24, 2009 Share Posted November 24, 2009 englbrechtfulWrote: "create a report groups that breaks every 5 minutes" You can write a method that truncates a date value to 5 minutes, place it in a utility class or in the report scriptlet and use it in the report as group expression.Regards,LucianCode:public static Date truncateTo5Mins(Date time){ Calendar cal = Calendar.getInstance(); cal.setTime(time); cal.set(Calendar.MILLISECOND, 0); cal.set(Calendar.SECOND, 0); int minutes = cal.get(Calendar.MINUTE); cal.set(Calendar.MINUTE, minutes / 5 * 5); return cal.getTime();} Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now