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

Are you saying that `12:35:15.293` becomes just `15.293`? Why would you want to do this? Or, do you want report the original amount as just numbers of seconds and milliseconds? — Tim Biegeleisen, Oct 10 '18 at 14:17
@TimBiegeleisen I think OP wants to convert hours and minutes to seconds also and show everything as seconds.microseconds — Madhur Bhaiya, Oct 10 '18 at 14:20

Absorbing · Accepted Answer · 2018-10-11T18:18:03.097

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

edited Oct 11 '18 at 18:18

answered Oct 10 '18 at 14:37

Absorbing

95
7

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

1 Answers1

Related