Page 1 of 1

Sum of time column

Posted: 2021-02-02 13:44
by ucevista
Hi.

I want to suggest the availability to sum time as total of a column as I can do with decimal values.

I have made an application for worksheets where I put the works I do to my customers, the time employeed and the kilometers I made to do that work. I can sum the total of kilometers (it is decimal field) but not the time. I think it will be very helpful becaus I can apply a filter and get the total amount of time.

As example there is this image with a filter applied (customer and date >= 01/01/2021 and date <= 31/01/2121):
Filer time.png
Filer time.png (91.57 KiB) Viewed 2772 times
Into this, I have technical actuations where I write the every thing I do and the time and kilometers and with a select I can sum the total of work for a day for a customer:

SELECT TIME_FORMAT(SEC_TO_TIME( SUM( TIME_TO_SEC(`tiempo`) ) ), '%H:%i') from parteactuacion WHERE `parte`='%ID%'

and that is what you see in the picture before.

Please, do you think this will be possible in the future?

Thanks a lot.

Re: Sum of time column

Posted: 2021-02-02 14:50
by onoehring
Hi,

+1 from me - a good idea.
I do see a problem how large numbers should be displayed though:
63:00 (as hours:min) or
3780 (minutes) or
2 days and 15 hours?
It's not so easy. Maybe the admin can choose which format should be used.

Olaf

Re: Sum of time column

Posted: 2021-02-03 06:59
by ucevista
Thanks Olaf.

I think it has to be displayed as HHH:mm:ss but I don't know if it will be configurable with data format to display as you like.

Re: Sum of time column

Posted: 2021-09-14 00:41
by peebee
I was confronted with the same requirement but solved it like this. 3 x fields required: start_time, end_time and total_time

start_time & end_time fields as "time" data format

total_time field as "decimal" (10,2) format.

In Appgini, set total_time as "caclulated field" and apply this SQL query

Code: Select all

SELECT TIME_TO_SEC(TIMEDIFF(end_time, start_time))/3600 as total_time 
from YOURTABLENAME 
WHERE `id`='%ID%'
Check the "Show column sum" checkbox for total_time.

End result:
time-calc.jpg
time-calc.jpg (9.46 KiB) Viewed 2393 times
(you can append the "hrs" after the hours in the column sum by adding this to hooks/YOURTABLENAME.php)

Code: Select all

 case 'tableview':
				$header="<%%HEADER%%><script type=\"text/javascript\">
				\$j(function(){
					\$j('td.YOURTABLENAME-total_time').last().append(' hrs');
				}); 
				</script>";
				break;
And obviously you need to replace YOURTABLENAME with your actual table name. Hope that may help somebody going forward.

Re: Sum of time column

Posted: 2021-09-14 05:10
by peebee
I should however add that there are limitations to the approach above in that "time" will only allow you to record 24 hrs max per entry and 11:59:59 PM is the upper limit time before you will revert to negative values. But it will work more than adequately for recording and summing general 24 hour time recording.

Re: Sum of time column

Posted: 2022-01-15 20:48
by ucevista
Hi Peebee.

Thanks a lot for your answer, it's a very nice solution. I'll try it.

Best regards.