Sum duration fields using GlideAggregate is not working

[Imran28]

Hi All,

I am using a GlideAggregate API to sum the values in the duration fields, I have written this script within a calculated value (within the field dictionary). I only get the value of the current record but not all the records and I have 28 records with value in the duration field.

Below is my script. 

(function calculatedFieldValue(current) {

var tables = current.getTableName();
var finder = current.getValue('with');// the value of the duration field
var val = 0;

var gr = new GlideAggregate(tables);
gr.addAggregate("SUM",finder);
gr.addNotNullQuery(finder);
gr.groupBy(false);
gr.query();
while(gr.next()){
gs.addInfoMessage(gr.getRowCount());
val = gr.getAggregate("SUM",finder);
gs.addInfoMessage(val);
}
return val; // return the calculated value

})(current);

Appreciate your guidance, Thanks in advance for your help.

Regards,
Imran

[Chuck Tomasi · Accepted solution]

Unfortunately your method won't work. Duration fields are actually date fields under the hood. It's storying the number of seconds elapsed since 1970-01-01 00:00:00 UTC in a date format. If your duration is 5 minutes, then the value in that field is 1970-01-01 00:05:00, thus you cannot sum dates.

You would need to query the field values and use the Glide Element methods dateNumericValue() and setDateNumericValue() to get/set the number of milliseconds on those fields. Here's a quick example

(function calculatedFieldValue(current) {

	var tables = current.getTableName();
	var val = 0;

	var gr = new GlideRecord(tables);
	gr.addNotNullQuery(duration_field);
	gr.query();
	
	while(gr.next()){
		gs.addInfoMessage(gr.getRowCount());
		val += gr.duration_field.dateNumericValue();
		gs.addInfoMessage(val);
	}
	
	return val; // return the calculated value in milliseconds

})(current);

[Rishabh Jha]

Surprisingly though, GlideAggregate 'AVG' on duration fields seem to work!