calculate a sum of type time using sql

Question

How to caculate sum of times of my colonne called "timeSpent" having this format: HH:mm in SQL? I am using MySQL.

the type of my column is Time.

it has this structure

TimeFrom like  10:00:00           12:00:00     02:00:00
TimeUntil      08:00:00           09:15:00     01:15:00
Time spent      

total time 03:15:00

Answer 1

Look at this post which I think covers what you are asking.

Answer 2

If the data type of the timeSpent column is TIME you should be able to use the following query:

SELECT SUM(timeSpent) 
  FROM YourTableName    -- replace YourTableName with the actual table name

However, if that is not the case, you may have to use a cast to convert to the Time data type. Something like this should work:

SELECT SUM(timeSpent - CAST('0:0:0' as TIME)) 
  FROM YourTableName    -- replace YourTableName with the actual table name

Answer 3

In MySQL, you would do something like this to get the time interval:

SELECT TIMEDIFF('08:00:00', '10:00:00');

Then to add the time intervals, you would do:

SELECT ADDTIME('01:00:00', '01:30:00');

Unfortunately, you're not storing dates or using 24-hour time, so these calculations would end up incorrect since your TimeUntil is actually lower than your TimeFrom.

Another approach would be (assuming you sort out the above issue) to store the time intervals as seconds using TIMESTAMPDIFF():

UPDATE my_table SET time_spent=TIMESTAMPDIFF(start, end));
SELECT SEC_TO_TIME(SUM(time_spent)) FROM my_table;