Aggregation on duration fields

[margie_joyce]

Hi there,

I'm wanting to do an aggregate query on a duration field, but I'm getting strange results.

I have a user defined table which has a duration field called u_effort. I want to sum the records for a particular user, for a particular date and for a particular type

var rec = new GlideAggregate("u_time_recording");
rec.addQuery("u_assigned_to", tsUser); <---- the user i am summing for
rec.addQuery("u_type", tsType); <---- the type i am summing for
rec.addquery("u_work_date", tsPeriodEnd); <---- the date i am summing for
rec.addAggregate("SUM", "u_effort"); <---- the field i am trying to sum
rec.groupBy("u_type");
rec.query();
if (rec.next()) {
var testField = rec.getAggregate("SUM", "u_effort");
gs.log("The aggregate value for " + rec.u_type + " is " + testField);
}

My data looks like this:

21-11-2008 1 day 15 minutes
22-11-2008 7 hours 30 minutes
22-11-2008 30 minutes

but the aggregate values i get out are always 1970-01-01 00:00:00 (which I read as essentially zero).

My questions:

1 - Is it possible to do aggregates on duration fields?
2 - If the answer to 1 is "Yes", what am I doing wrong here?
3 - If the answer to 1 is "No", how would I go about manually summing duration fields?

Best regards and thanks,
margie

[merbs]

I have an answer!

I needed to convert the duration into milliseconds.

I created a variable, and then queried my data; then converted the duration to milliseconds and added it to the variable. Worked like a charm

var waitTime = 0;

gr query here...

while(gr.next()) {

  waitTIme += gr.wait_time.dateNumericValue();

  }

gs.print(waitTime/1000) //converts back to seconds