Timetracker using Calculated Fields

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
hgarbe
Veteran Member
Posts: 57
Joined: 2020-01-21 17:35

Timetracker using Calculated Fields

Post by hgarbe » 2021-02-25 00:33

Hi everybody,

I wanted to create a Timetracker to document time spent on different things. I found a partially working solution, but it has a bug. As I just got a very basic understanding of SQL I hope someone can give me a hint how to handle it.
I have two tables. Table 1 (itm_timetracker) is calcuating the timedifference between 2 datetime fields using this code:

Code: Select all

SELECT TIME_FORMAT(TIMEDIFF(`itm_timetracker`.`end_time`, `itm_timetracker`.`start_time`), '%H:%i') FROM `itm_timetracker` 
WHERE `itm_timetracker`.`id`='%ID%'
This code works and is displaying the Timediff in the way I wanted it to be.
timediff.png
timediff.png (3.34 KiB) Viewed 1313 times
The calculated Field in the second Table uses the code below so sum up all Timediffs based on an associated Task ID.

Code: Select all

SELECT TIME_FORMAT(SUM(`itm_timetracker`.`time_consumption`), '%H:%i') FROM `itm_timetracker` 
WHERE `itm_timetracker`.`associated_task`='%ID%'
Works fine until the sum of minutes exceeds 59 or in other words is more than one hour. As said I'm still working my way through SQL so any help appreciated!

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Timetracker using Calculated Fields

Post by pbottcher » 2021-02-26 22:04

Hi,

try

Code: Select all

SELECT TIME_FORMAT(SEC_TO_TIME(SUM(Time_TO_SEC(`itm_timetracker`.`time_consumption`)))), '%H:%i') FROM `itm_timetracker` 
WHERE `itm_timetracker`.`associated_task`='%ID%'
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

hgarbe
Veteran Member
Posts: 57
Joined: 2020-01-21 17:35

Re: Timetracker using Calculated Fields

Post by hgarbe » 2021-03-01 00:06

Thanks alot pbötter!
For taking the time to respond and at the same time solving the problem!
So the simply way was just to convert it to seconds, sum it up and then reconvert it?

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Timetracker using Calculated Fields

Post by pbottcher » 2021-03-01 06:54

Yep, true
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

Post Reply