CODEWITHSUNDEEP
mysqltimesum
Valerius Romulus
Valerius Romulus

Reputation: 55

SUM hours.minutes as total correct hours.minutes

I'm looking for a string (mysql) that could sum couple of hours.minutes for total timecorrectly. I cannot find anything right.

Example:

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

I Can sum this by:

Select sum(time_spent) as total_hours from table

but the result is: 50:75 but should be like 51:15

I've tried also this:

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

but the result is: 00:00:50

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

but the result is: 00:00:50.750000

Is there any possible to get a total correct hours.minutes from these above records?

Upvotes: 2

Views: 319

Answers (2)

Fabian N.
Fabian N.

Reputation: 1240

My suggestion would also be use an appropiate data type (like time). But if you are stuck with this idea of having an varchar, you could convert your time in seconds, summing it up and convert back.

TIME_TO_SEC would expect the format HH:MM, but you have some fancy floating point there, so your best shot would be parsing:

TIME_TO_SEC(SUM(STR_TO_DATE(time_spent,'%H.%i')));

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

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 |
+----------+

Upvotes: 1

Related Questions