Search code examples
mysqldatetypeconverter

Is any way to convert decimal to time in MySQL?

I created a field called 'hours_spent' in MySQL using the decimal datatype to store time. The values are stored like this 1.30, 2.30 etc... (for 1hr30min, 2hr30min).

I want to calculate the sum of various time values.

The sum of time is not what I expected: 1.30 + 2.30 = 3.60, whereas I expected 4.00.

I used the SUM function in MySQL to count the hours_spent field. If the values are 0.30 + 1.50 = 1.80, whereas I expected 2.20.

My first mistake was to use the decimal type instead of the time datatype, but I cannot change datatype.

So, is there any way to sum the time values and get result as I expect?

Thanks

Solution

  • I prepared you a demo at sqlfiddle, you can try it there if you want:

    http://www.sqlfiddle.com/#!2/c9afc/2

    Here are the query samples:

    select @indexer:=instr(dateasdecimal, '.')
, left(dateasdecimal, @indexer-1) * 60 + substr(dateasdecimal, @indexer+1)  as totalMinutes
from testtable;

select @indexer:=instr(dateasdecimal, '.')
, sum(left(dateasdecimal, @indexer-1) * 60 + substr(dateasdecimal, @indexer+1))  as totalMinutes
from testtable;

    Note: Please don't forget to accept answers to your questions: https://meta.stackexchange.com/a/65088/200585