CODEWITHSUNDEEP
mysqltimemillisecondssecondsdatetime-conversion
Isabel
Isabel

Reputation: 37

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

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 :)

Upvotes: 2

Views: 1915

Answers (1)

Absorbing
Absorbing

Reputation: 95

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

Upvotes: 1

Related Questions