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
  • 615 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

As pointed out, the root cause is that DECIMAL storing values like 1.30 is not a base‑60 time: the part after the decimal is treated as a base‑10 fraction by arithmetic. To get correct carry from minutes to hours you must convert each value into a single unit (minutes or seconds), sum, then reformat.

A compact SQL approach (no schema change) converts each DECIMAL to seconds, sums, then returns a proper time:

SELECT SEC_TO_TIME(
  SUM(FLOOR(time_decimal) * 3600 + ROUND((time_decimal - FLOOR(time_decimal)) * 100) * 60)
) AS total_time
FROM your_table;

If you need the old "H.MM" look (for backward compatibility) you can sum minutes and format:

SELECT CONCAT(FLOOR(total_minutes/60), '.', LPAD(MOD(total_minutes,60),2,'0')) AS total_h_mm
FROM (
  SELECT SUM(FLOOR(time_decimal) * 60 + ROUND((time_decimal - FLOOR(time_decimal)) * 100)) AS total_minutes
  FROM your_table
) AS s;

If you prefer doing it in PHP after fetching rows, convert each value to minutes, sum, then format:

$total_minutes = 0;
foreach ($rows as $r) {
    $v = (string)$r['time_decimal'];            // "1.30" or "0.40"
    if (strpos($v, '.') !== false) {
        list($h, $m) = explode('.', $v, 2);
        $m = str_pad(substr($m, 0, 2), 2, '0', STR_PAD_RIGHT);
    } else {
        $h = $v; $m = '00';
    }
    $total_minutes += intval($h) * 60 + intval($m);
}
$hours = floor($total_minutes / 60);
$minutes = $total_minutes % 60;
echo sprintf("%d.%02d", $hours, $minutes);

Notes and cautions: validate that stored "minutes" are 00–59 (fix/normalize any rows where they are not) and prefer a long‑term change to a proper TIME column or storing total minutes as an integer. To avoid the ALTER pitfalls you saw, add a new TIME column, populate it with a conversion update (as above), verify, then drop/rename.

Recommended Answers

All 5 Replies

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.