Sum of time column

Wish to see a specific feature/change in future releases? Feel free to post it here, and if it gets enough "likes", we'd definitely include it in future releases!
Post Reply
ucevista
Veteran Member
Posts: 30
Joined: 2016-05-08 09:41

Sum of time column

Post by ucevista » 2021-02-02 13:44

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 2760 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.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Sum of time column

Post by onoehring » 2021-02-02 14:50

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

ucevista
Veteran Member
Posts: 30
Joined: 2016-05-08 09:41

Re: Sum of time column

Post by ucevista » 2021-02-03 06:59

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.

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 352
Joined: 2013-03-21 04:37

Re: Sum of time column

Post by peebee » 2021-09-14 00:41

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 2381 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.

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 352
Joined: 2013-03-21 04:37

Re: Sum of time column

Post by peebee » 2021-09-14 05:10

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.

ucevista
Veteran Member
Posts: 30
Joined: 2016-05-08 09:41

Re: Sum of time column

Post by ucevista » 2022-01-15 20:48

Hi Peebee.

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

Best regards.

Post Reply