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开发者_Python百科 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
SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( `timeSpent` ) ) ) AS timeSum
FROM YourTableName
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.
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;
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
精彩评论