How to convert time from hh:mm:ss.mmm to seconds and milliseconds mysql

Question

I would like to convert all values in a column from "hh:mm:ss.mmm" to "ss.mmm". I have tried TIME_FORMAT(Duration, 's%.f%) but this shows the seconds and milliseconds and doesn't keep calculate the correct seconds when values are over 1 minute.

Alternatively I could use the difference between the start and end columns, as long as it returned the time in "ss.mmm". I have tried this using TIMESTAMPDIFF(microsecond,Start,End)as diff but it is to milliseconds and I don't know how to convert it to "ss.mmm". And when specifying second instead of microsecond it loses the milliseconds.

Thanks :)

Answer 1

This should do what you're asking, if I understand correctly you already have the data containing milliseconds.

I don't think MySQL has an inherent millisecond function, but we can use microseconds instead.

SELECT CONCAT(TIME_TO_SEC('15:30:15.293'), '.', TIME_FORMAT('15:30:15.293', '%f'));

This will output as below;

55815.293000