Timetracker using Calculated Fields
Posted: 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:
This code works and is displaying the Timediff in the way I wanted it to be.
The calculated Field in the second Table uses the code below so sum up all Timediffs based on an 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!
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%'- timediff.png (3.34 KiB) Viewed 2020 times
Code: Select all
SELECT TIME_FORMAT(SUM(`itm_timetracker`.`time_consumption`), '%H:%i') FROM `itm_timetracker`
WHERE `itm_timetracker`.`associated_task`='%ID%'