how to aggregate datetime values

I have a database with columns of datetime value which contain information such as duration (time spent by a user on an application session for instance).

I have a report with several level of grouping, and for each group level I would like to aggregate the duration (such as total time spent on an application by a user - sum of all sessions duration).

I have defined an aggregate function "SUM" on row["TOTAL_TIME"]. If I use type "date", "time" or "date time" in data type of aggregation builder, I get exception at run time. And if I use "float" (I get a message to do so), the report runs but displays values such as -2,142E7.

Please tell me how to do what I want to do.

Find more posts tagged with

Comments

mwilliams

Hi ahsijetais,

What does the data in row["TOTAL_TIME"] look like? Do you keep track of the duration in seconds, minutes, days, or something else?

ahsijetais

It is a MySQL database and the exact datatype of the column is "TIME". When I run a SELECT query in MySQL Browser on that column, I get values such as :

00:00:25
00:00:35
00:01:15

In this example, I would like to aggregate and get 00:02:15. (25 seconds + 35 seconds + 1 minute 15 seconds = 2 minutes 15 seconds).

mwilliams

ahsijetais,

There may be other ways to do this as well, but I was able to do this by adding an aggregation to the table using runningsum and float as the type. For my expression, I used:

row["Time"].getHours() * 3600 + row["Time"].getMinutes() * 60 + row["Time"].getSeconds();

This takes each row's hours, minutes and seconds and makes them into total seconds. As long as the aggregation is bound to you table, you don't have to display this value. You can display the total seconds as a time type in a data item with the expression:

new Date(0,0,0,0,row["Aggregation_Name"]);

Then you format the data item with a custom format of "HH:mm:ss". Let me know if you have any questions.

ahsijetais

Thanks for your detailed answer. I have done what you said, but I don't get the results I expect.

I have attached the report file and a zip with the pdf I get when I run the report. I want to aggregate the "total time" of each "session" for all "sessions" related to the same "sequence". The fist total is correct, the other are wrongs.

Where did I go wrong?

mwilliams

ahsijetais,

Without being able to run and test, it's tough for me to tell. Try changing your "Aggregate on" group to the "Sequence" group rather than "Session" and see if that helps. If you're wanting the time over the sequence, you should aggregate over it. Let me know what that does. If that doesn't work, maybe you can give me a flat file of data, so I can run the report and troubleshoot it. Thanks.

ahsijetais

Hello Michael,

I found where was the mistake. In each row of data, I have a field "TOTAL_TIME", which is the duration of a "session", and a field "LATENCY", which is the duration of an "interaction" during a "session". This is required since "TOTAL TIME" of a session is not the sum of "LATENCY" of all its interactions.

Therefore, if I want to compute the "total time" of all the "sessions" which relate to the same "sequence", I have to sum "TOTAL_TIME" of all these "sessions". But if I do the aggregation at the row level, I get :

total time for a session = TOTAL_TIME of the session * number of interactions during this session

which of course is not correct.

I can't do an average since all the sessions do not have the same number of interactions. So is there a way to aggregate so the result would be correct?

For your information, I run a test using your guidelines and doing the aggregation of the field "LATENCY", and everything was fine. So despite it is not the information I want to provide in this report as I mentioned earlier, your information was very valueable for aggregation on time values.

mwilliams

ahsijetais,

Can you give me a flat file section of data so I can change out the dataSet in the report you attached here and run the report myself? Exactly how it looks in your dataSet. Thanks.

ahsijetais

Michael,

you will find attached a zip file contaning a CSV file which is a full extract of the resultset I use for testing.

This resultset contains data coming from joined tables with the following relationships :

a course uniquely identified by a COURSE_NAME contains one or more
course elements, each of them uniquely identified by COURSE_ELEMENT_NAME,

on each COURSE_ELEMENT_NAME, users uniquely identified by USER_NAME can run one or more sessions and each of these sessions has a duration of TOTAL_TIME

during each session, the user can have one or more interactions, each interaction has a duration of LATENCY, but TOTAL_TIME is not the sum of all the LATENCY

I want to display in my report for each user :

the sum of TOTAL_TIME of all his sessions for each COURSE_ELEMENT_NAME,

the sum of TOTAL_TIME of all his sessions on all COURSE_ELEMENT_NAME associated to the same COURSE_NAME.

mwilliams

ahsijetais,

Ok, I have it set up to run for me now with your flat file. So, "Total Time" isn't supposed to be the addition of all the Latency values for that section? But, you want The sequence time to add the "Total Time" values for that section correctly? Is my understanding correct?

ahsijetais

Hello Michael,

This is it: "Total Time" isn't the addition of all the Latency values for that section, and I want the sequence time to add the "Total Time" values for that section correctly.

In other words:
for each sequence, a user can run one or more sessions.
each session has a duration of "total time".
for each session, a user can have one or more interactions.
each interaction has a duration of "latency".
I want the sequence time to add the "total time" of all sessions, not the total time of all interactions of all sessions (which is easy to do with an aggregate).

thanks in advance.

mwilliams

ahsijetais,

I think I may have the solution you're looking for. It involves an extra aggregation on your table to eliminate the duplicate values that are being added in each session.

One aggregation should be on the group Session:

Type: Integer
Function: First
Expression: row["TOTAL_TIME"].getHours()*3600+row["TOTAL_TIME"].getMinutes()*60+row["TOTAL_TIME"].getSeconds();

The next aggregation should be on group Sequence:

Type: Float
Function: SUM
Expression: row["first_aggregation_name"]

Finally, you have your data item in your report:
Type: Time
Expression: new Date(0,0,0,0,0,row["second_aggregation_name"])

This gave me the correct sum of session times for each sequence. Let me know if this works for you. Thanks.

ahsijetais

We are almost done, thanks Michael.

I have added the two aggregations as you mentioned. It works fine and I get the correct sum of session times for each sequence.

I have just one problem left, which is the correct display in hours:minutes:seconds of that value.

I have added the data item as you mentioned, but I get a wrong display. I have attached a ZIP file with the PDF of the report I get.

In the group 'sequence', on the right of the field 'meilleur resultat obtenu', I display the aggregate as an integer for testing purposes, and the value is correct (for instance, 667 for the fist sequence).
In the same group, on the right of the field 'total temps passe', I display the aggregate as you mention (type time, expression new Date..., format HH:mm:ss), and I get 23:30:28, although the correct display should be 00:11:07.

mwilliams

ahsijetais,

I am not sure why you would be getting that. I am getting 00:11:07 as expected. Can you attach your latest report design so I can take a look?

ahsijetais

please find attached the report file corresponding to the PDF sent previously.

mwilliams

ahsijetais,

There's an extra '+' in your first aggregation. Not sure if that would be causing problems or not. May be something to check out at least. Otherwise, at first glance, I don't see anything that would cause that.

ahsijetais

just remove the extra '+' in the aggregation and nothing changed on report output...
Notice that when I display the value as Integer it is correct (667 for first sequence). When I display it as Time with the Date function it is not.
I am running a french version of Windows XP, do I have to change something in the environment?

mwilliams

ahsijetais,

I don't think you should have to change anything. The new Date() function should work the same no matter what. If you put new Date(0,0,0,0,0,0), what does it do? It should be 00:00:00. If so, you could try to add the seconds another way like the following:

BirtDateTime.addSecond(new Date(0,0,0,0,0,0),row["Aggregation"])

Another thing to do may be to delete the binding from the table and from the binding tab in the property editor for the table and recreate it. Maybe there was some sort of glitch with that particular data element.

Here is what my output looks like, both with the data binding how I originally told you and this new way above.

ahsijetais

I tried with new Date(0,0,0,0,0,0) and I get... 23:09:21.

I tried with new Date() and it displays current time correctly.

ahsijetais

I tried with new Date(0,0,0,0,0,0) and type 'Datetime' and I got :
30 december 1899 23:09:21

sounds like a configuration parameter to change somewhere?

mwilliams

ahsijetais,

For me, new Date(0,0,0,0,0,0) give me December 31, 1899 12:00:00 AM...which is expected. Yours for some reason is 00:50:39 behind that. Try this expression in the type Time element with format HH:mm:ss.

dt = new Date();
dt.setHours(0,0,row["Aggregation"],0);
dt;

Again, this works for me.

ahsijetais

Hello Michael.

It did not work so as I don't know where the problem comes from, I ended up by writing few lines of javascript for that element to display the number of seconds, which is correct, as hh:mm:ss.

It works fine, thanks for your help on setting up properly the aggregations.

I have another issue : using localization files in scripting.

I am using BIRT 2.3.1 an running on Tomcat 5.5. Localization files work fine for keys associated to labels directly in localization tab of property editor.

For scripting I am using syntax such as:

this.text = reportContext.getMessage("ModuleValidationRuleMinScorePercentLabel",reportContext.getLocale());

where "ModuleValidationRuleMinScorePercentLabel" is a key defined in my resource file. But it does not work, nothing is displayed.

I have also tried :

this.text = reportContext.getMessage("ModuleValidationRuleMinScorePercentLabel");
It does not work either.

The reason why it did not work was that the resource files were not in the birt home directory. I placed them there and everything works fine.

mwilliams

Glad you got it working.

mohanish

Hi mwillaims ... 
 
Thanks for the post it was really helpful.I have a small issue on the above post ... Suppose lets say i have a time as 26 hrs :11 min :07 secs .. what birt is doing after changing the date format to HH:mm:ss is 02:11:07 which means 1day 02 hrs :11 min :07 secs but i don't see 1day after using the format HH:mm:ss ..Is it possible to get 26hrs or 1day anyhow or is there any workaround.Please help me out buddy .... 
 
thanks a lot :-)

mwilliams

You should be able to format your output in a string to get the same look. You'd still have the original binding for anywhere you need to compute with a date.