Member Avatar for venkyb47

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?

Thanks

  • 2 Contributors
  • 5 Replies
  • 581 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for pritaeas

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.

Member Avatar for venkyb47

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.

Member Avatar for pritaeas

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.

Member Avatar for venkyb47

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

Member Avatar for pritaeas

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, networking, learning, and sharing knowledge.