calculate sum time with mysql

Question

Possible Duplicate:
calculate a sum of type time using sql

Third duplicate - please stop & focus on the original question.

RDBMS: mysql

column names: Timefrom,timeuntill, timespent as the following

type of the column:Time.

timefrom timeuntill timespent

10:00:00 12:00:00 02:00:00

08:00:00 09:00:00 01:00:00

how could i get the sum of the timespent. like this example it would be 03:00:00. when doing select sum(timespent) from mytable it display: 030000.

please help. thanks.

Answer 1

Try this:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(timespent))) FROM yourTable;

Ref: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-type-overview.html

Answer 2

you will need to parse out the components of the time value, then normalize to one single unit, then sum on that unit, and possibly reformat back into the time components for display.

e.g. 02:22:54 would be parsed into 02 * 360, 22 * 60, and 54

then those would be added together to find the total number of the smalles unit (seconds?)