Is any way to convert decimal to time in MySQL?

Question

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

Tournament

Great question! I would know how to do this using PHP, but To be clear you want these calculations done in directly in mysql? I'm very interested to hear the answer.. – Disseise

You may have to write a custom code for the same. – Zach

Ok iight. I fetch data from mysql and do in PHP. Is any predefined function available in PHP? or what is the way? – Tournament

Answer 1

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

Shovel

Answer 2

To convert a decimal into seconds, you could use this:

truncate(hours_spent,0)*60+(hours_spent-truncate(hours_spent,0))*100

and then you can do the sums easily. Then you can convert back seconds to the decimal format with this:

truncate(seconds/60,0)+truncate(mod(seconds, 60)/100,2)

Joub

Answer 3

You could always turn the decimals into a string, cast as time, then sum that time using time_to_sec and produce a formatted time with sec_to_time. Of course, it would be much better to be storing those times a different way, even if it involves converting the entire dataset.

SELECT sec_to_time(sum(time_to_sec(goodTime))) FROM (   
    SELECT CAST(badTime AS TIME) AS goodTime FROM ( 
        SELECT REPLACE(badTime, '.', ':') AS badTime FROM (
            SELECT CAST(badTime AS dec(4,2)) AS badTime FROM (
                SELECT 1.3 AS badTime 
                UNION select 2.3 
            ) z
        ) y
    ) x
) w

Ammonic