MySQL SUM time(3) with milliseconds

Question

I have mysql column time(3) and it's storing good value of time.. but then I want to sum two times it converts to bad time format;

I have two records:

id | time
---|-----------
1  | 00:00:15.490
2  | 00:02:14.900

So in real I shoud get: 00:02:30.390

but I get 230.390

is anyway to get correct answer from Mysql? P.S. I am using php for functions but dont want to use it, unless there is other way. Need to sum times with MILLISECONDS

for now I am using query SELECT SUM(time) AS total_time FROM times WHERE 1

Answer 1

Provided your table definition is something like this:

create table test (
  id integer,
  `time` time(3)   -- important to specify precision
);

You can do this:

select time(sum(`time`))
  from test;

note: requires mysql 5.6+ edit

Actually, time is the wrong function to use, as it doesn't have many smarts.

use sec_to_time instead, ie:

select sec_to_time(sum(`time`))
  from test;

time extracts a time value, sec_to_time calculates a time value -- ie, time(70) returns NULL because there's no valid time that has 70 seconds, where as sec_to_time will correctly return '00:01:10' for the same input edit

Turns out i'm still wrong. Lets try treating the milliseconds separately to the rest of the time:

select sec_to_time(sum(time_to_sec(`time`)) + sum(microsecond(`time`))/1000000)
  from test;

Answer 2

Wrap your outputted result with the time function. So:

time(sum(`time`))

where time is the time function and 'time' is your summed column.