I am using Decimal data type to store time like 1.30(1 hour 30 min),0.40(40 minutes) in MySQL. I

want to calculate total time. When I use SUM function it was giving result in unexpected way.

example:  1.30 + 0.30 = 1.60   -> SUM function gives like this.

But I want 2.00 as result.
Is any way to get result like this?


Recommended Answers

All 5 Replies

Calculate before and after the comma separately. What's behind the comma needs to be handled differently. The result of the SUM is just the usual decimal addition. See this page, look for elapsed.

Thanks for reply.
From the link you given I understand that I need to use time datatype instead of decimal. Is any way to convert decimal to time.

You can try an ALTER, but I'm not sure about the conversion issues, and I cannot try right now. You can add a new column with the time datatype, perform an update to fill it correctly, then remove the old column, and rename the new.

When I try ALTER for decimal to time, the data was changing completely to different format like 00:00:00.
So without changing datatype is any other way to do in php(I mean after fetching data)?

Yes, you can do my first reply in PHP.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.