Description:
The current MySQL engin'e SUM() function cannot sum datetime fileds or grouped timediff() function results. SUM() currently returns an int representing the hours of the timediff.
I am also looking for a timediff function that returns DATETIME format instead of just TIME format. I wanted the results broken down into days, months, years, assuming 30-day months. If assuming is a bad idea, then OK.
How to repeat:
SELECT id,name, prj,sum(timediff(end_time,start_time)) AS elapsed
FROM work
group by name,prj;
CREATE TABLE work (
id INT auto_increment NOT NULL,
name VARCHAR(45) NOT NULL,
start_time DATETIME NOT NULL,
end_time DATETIME NOT NULL,
tasknum INT DEFAULT NULL,
prj VARCHAR(45) DEFAULT NULL,
PRIMARY KEY(id)
);
Suggested fix:
At least have the sum function return results in the type (or a similar type) that the data came in.