CODEWITHSUNDEEP
mysqlsqlmath
JJeff
JJeff

Reputation: 129

MySQL convert legacy decimal to milliseconds

I am working with some legacy tables that represent time as a decimal representating time like this:

74447.548  = 7:44:47.548

I am moving this to a table where time is stored as (int) milliseconds. I want to create a function for this conversion.. The following works, but is there a more efficient way??

CREATE FUNCTION `test`.`decimalToMilli` (bigTime decimal)
RETURNS INTEGER
BEGIN
return (floor(mod(bigTime,floor(bigTime))*1000)  -- milliseconds
  + (floor(bigTime) MOD 100)  * 1000 -- seconds
  + ((((floor(bigTime) - floor(bigTime) MOD 100) MOD 10000))/100) * 1000*60  -- minutes
  + ((((floor(bigTime) - floor(bigTime) MOD 10000) MOD 1000000))/10000) * 1000*60*60 --hrs
);
END

Suggestions for a better way?

Upvotes: 1

Views: 176

Answers (1)

Marlin Pierce
Marlin Pierce

Reputation: 10079

What about this?

CREATE FUNCTION `test`.`decimalToMilli` (bigTime decimal)
RETURNS INTEGER
BEGIN
  return ((bigTime * 1000) MOD 100000  -- seconds and milliseconds
         + (floor(bigTime / 100) MOD 100) * 60000 --minutes
         + (floor(bigTime / 10000)) * 3600000 -- hours
         );
END

Upvotes: 2

Related Questions