CODEWITHSUNDEEP
sqlmysql
kawtousse
kawtousse

Reputation: 4141

calculate a sum of type time using sql

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

Upvotes: 47

Views: 70486

Answers (4)

Siri
Siri

Reputation: 1414

SELECT  SEC_TO_TIME( SUM( TIME_TO_SEC( `timeSpent` ) ) ) AS timeSum  
FROM YourTableName

Upvotes: 110

Developerscentral
Developerscentral

Reputation: 127

100% working code to get sum of time out of MYSQL Database:

SELECT
SEC_TO_TIME( SUM(time_to_sec(`db`.`tablename`))) 
As timeSum
FROM
`tablename`

Try and confirm. Thanks.

Upvotes: 7

Lèse majesté
Lèse majesté

Reputation: 8045

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;

Upvotes: 3

Mike Dinescu
Mike Dinescu

Reputation: 55730

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

Upvotes: 0

Related Questions