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):
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.
Sum of time column
Re: Sum of time column
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
+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
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: Sum of time column
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.
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
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
Check the "Show column sum" checkbox for total_time.
End result: (you can append the "hrs" after the hours in the column sum by adding this to hooks/YOURTABLENAME.php)
And obviously you need to replace YOURTABLENAME with your actual table name. Hope that may help somebody going forward.
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%'
End result: (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;
Re: Sum of time column
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
Hi Peebee.
Thanks a lot for your answer, it's a very nice solution. I'll try it.
Best regards.
Thanks a lot for your answer, it's a very nice solution. I'll try it.
Best regards.