Automatic SQL calculation/insert possible?

Databases
1

time_track table

row_id = int
member_id =int
task_id = int
start_time = datetime
end_time = datetime
total_task_time = decimal

SELECT end_time-start_time from time_track where task_id=number

This will calculate the difference in seconds and output, but I was wondering is there any way for it to automatically do this, divide by 3600 (to get into hours) and insert it into the total_task_time? Not sure if this is possible. Please let me know :slight_smile:

Thanks!
Aaron

2

Thanks for your quick response! I am using MySQL. My coding is in php, so you would say it would be better to allow the php code to do it rather than having it go straight into the database like through a default value?

3

Yes, though really depends on platform.

Iā€™d generally handle this in the application layer though.

4

Sorry for bumping again. I figured it out I used the SELECT TIMEDIFF(end_time, start_time) FROM time_tasks where row_id=1

outputs:

00:02:25 does the conversion for you :slight_smile:

5

Thanks rudy! I found that as a solution as well but the TIMEDIFF function seemed a little easier for my scenario

6

time format is good too :slight_smile:

7

This goes along the same lines but in mysql query when I do select end_time-start_time from time_track where row_id=1 for example it gives me ā€œ113.0ā€ meaning 1 minute 13 seconds is there a way to switch that formatting because you cant just do /60 in that case you would have to take the first number and then add a decimal for example 1.(13/60)

8 
SELECT ( TIME_TO_SEC(end_time) - 
         TIME_TO_SEC(start_time) ) / 3600.00 AS hours
  FROM ...