Calculating Time Total in Grid

Applications Grid / Reports
#1

I wonder if anybody could give me any ideas as to how to calculate the total time on a grid ‘Group By’, and would also be nice to utilise the same method on a PDF Report later on.

Put simply, I have a series of records, each have a start and end DATETIME field, and I am calculating the difference between the times using TIMEDIFF and then inserting that in to the DB against each record every time the grid loads to ensure it is up to date, however… what I cannot work out how to do, is to calculate the total time of all records on the ‘Group By’ selections as this only gives the option of COUNT for time fields.

Is there a simple way of this, or does the total time need to be converted to seconds to calculate as a SUM and then converted back to time format, and if so, what is the best method within scriptcase?

Thanks in advance

#2

I agree, this would be very helpful and I haven’t yet found a solution to it.

#3

Just make a new custom field, set it as integer (ACCUMULATED checkbox shoul appear now), set ACCUMULATED and select the field to accumulate.

That simple

#4

Hello!

I tested the solution, but somehow it doesn’t work. What am I doing wrong?

In my grid I do a grouping by device_id and checkall_res. I created a new custrom-field result and set it to integer and ACCUMULATED.
But I don’t get my TimeDiff field when I select it, only if I also set field TimeDiff also to integer or decimal.
Then under group by, I can add my field to sum and select sum.

But the result shown in grid is always 0 (zero) or nothing.

Here is the Sql:

Select
stat.device_id,
checkall_res,
stat.ts,
stat.tsbis,
TimeDiff(stat.tsbis, stat.ts),
stat.stat_id
From
stat
Order By
stat.stat_id Desc

And these are a few values that the SQL returns.

device_id, checkall_res, ts, tsbis, TimeDiff(stat.tsbis, stat.ts), stat_id

‘5C244D1FE924’, ‘20’, ‘2019-09-11 15:43:31’, ‘2019-09-11 15:43:31’, ‘00:00:00’, ‘412897’
‘5C244D1FE924’, ‘10’, ‘2019-09-11 15:43:29’, ‘2019-09-11 15:43:31’, ‘00:00:02’, ‘412896’
‘A480389DBEEB’, ‘10’, ‘2019-09-11 15:41:16’, ‘2019-09-11 15:41:16’, ‘00:00:00’, ‘412895’
‘5C244D1FE924’, ‘20’, ‘2019-09-11 15:41:15’, ‘2019-09-11 15:43:29’, ‘00:02:14’, ‘412894’
‘5C244D1FE924’, ‘20’, ‘2019-09-11 15:41:13’, ‘2019-09-11 15:41:15’, ‘00:00:02’, ‘412893’
‘A480389DBEEB’, ‘10’, ‘2019-09-11 15:40:46’, ‘2019-09-11 15:41:16’, ‘00:00:30’, ‘412892’

Thanks for helping!

#5

The way I do it is to change all dates into unix time (seconds from 1970) ; In my case I even had different time formats - one was 2019/09/01 00:00:00 the other 2019-09-01 00:00:00. So I formatted them first

$format_timestamp = date('Y-m-d H.i.s', strtotime($timestamp));//from javascript 
$unix_date = strtotime($date); //from Mysql
$unix_date2 = strtotime($format_timestamp);
$seconds = $unix_date2 - $unix_date;
$now = time(); //this is already unix should you need to calculate time elapsed.

You can then subtract and add them together - the result is seconds . Finally if you want to display the seconds in days minutes and seconds (without saving them to the db that way) I use

$hour = floor({duration} /3600);
$min = floor(({duration} - ($hour *3600)) /60);
$sec = {duration} % 60;
if($hour < 10){$padhour = "0";}else {$padhour = "";}
if($min < 10){$padmin = "0";}else {$padmin = "";}
if($sec < 10){$padsec = "0";}else {$padsec = "";}
{duration} = $padhour . $hour . ":" . $padmin . $min .":" . $padsec . $sec;

I think there may be an easier way to do this last part but fundamentally if you put this in the onRecord part of the grid you will see days hours minutes but the db will have seconds. And therefore you can add them in the normal way.

#6

Sorry, for the late response.
Thanks, not the easiest way, but working :slight_smile:

#7

Agreed, I tried some built in SC methods but they were just as awkward. I curse at the fact that JS, MySQL, Unix and MySQL have different format (plus their international variants) . If you move from one to another, as I do, it makes it easier to always think in seconds which leads me to the Unix timestamp method.