mysql - SUM hours.minutes 作为总正确的 hours.minutes

Question

我正在寻找一个字符串 (mysql)，它可以正确地计算总时间的几个小时。分钟。我找不到任何合适的东西。

例子:

id|  time_spent|
1 |  10.15     |
2 |  10.15     |
3 |  10.15     |
4 |  10.15     |
5 |  10.15     |

我可以总结为:

Select sum(time_spent) as total_hours from table

但结果是:50:75 但应该像 51:15

我也试过这个:

SELECT SEC_TO_TIME( SUM( SEC_TO_TIME( `time_spent` ) ) ) 
AS total_time FROM table

但结果是:00:00:50

SELECT SEC_TO_TIME(SUM(SECOND(time_spent))) AS total_time FROM 
table

但结果是:00:00:50.750000

是否有可能从上述记录中得到正确的总小时数和分钟数？

Answer 1

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, t TIME NOT NULL);

INSERT INTO my_table (t) VALUES 
('10:15:00'),
('10:15:00'),
('10:15:00'),
('10:15:00'),
('10:15:00');

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(t))) x FROM my_table;
+----------+
| x        |
+----------+
| 51:15:00 |
+----------+