MySQL TOTAL TIME (3) with milliseconds

Question

I have a mysql (3) column time and it saves a good time. but then I want to summarize twice when it converts to a bad time format;

I have two entries:

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

So, in real life I get: 00:02:30.390

but I get 230.390

Anyway, to get the correct answer from Mysql? Postscript I use php for functions, but don't want to use it if there is no other way. Need to summarize the time with MILLISIONS

I am currently using the query SELECT SUM(time) AS total_time FROM times WHERE 1

Answer 1

If your table definition looks something like this:

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

You can do it:

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

Note: mysql 5.6 + required change

In fact, time is the wrong function to use, since it does not have a lot of skills.

use sec_to_time , namely:

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

time retrieves the time value, sec_to_time calculates the time value, i.e. time(70) returns NULL because there is no valid time that has 70 seconds, where sec_to_time will correctly return '00:01:10' for the same input

Turns out I'm still wrong. Let's try to process milliseconds separately until the end of time:

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

Answer 2

Wrap the result using the time function. So:

 time(sum(`time`)) 

where time is a function of time, and "time" is your summary column.