Sepcial Chart with Time Axis

[englbrechtful]

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

[lucianc]

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

[englbrechtful]

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 )
 

 

[englbrechtful]

Please Lucian!

 

I dont know how to create a "breaker" that breaks every 5 Minutes

 

:(

[lucianc]

englbrechtful
Wrote:

"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,

Lucian

Code:

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();}