[RESOLVED] Need to add time together (aka SUM)

stormszero

Hello,
I got a question which I hope you can answer. It is MySQL related. I have no problems with the PHP implementation - but here goes:

Assume a regular column with integer values like:

Columnname = SOME_INTS
1
4
2
3

"SELECT SUM(SOME_INTS) FROM table" will yield 10.

Now, for the problem.
I have a table called systemErrors.
In this table, one of my colums are named "ELAPSED_TIME" and is of type TIME (hh:mm:ss).

I would like these added together SUM-like - this means, add the entire column and return the end result. But can anyone help me doing this? If I SUM the column, I get a result as "hhmmss" and not "hh:mm:ss" as it needs to be. I have tried to look at ADDTIME but it doesn't - atleast to my knowledge - take the entire column (it takes two arguments).

To see what I mean, I have an example here with SUM:

   01:06:31
+  02:24:15
+  02:02:56
=  53302 (SUM doesn't return as hh:mm:ss but as hhmmss)

The reason I so badly want this done is because the data must be read by thirdparty and needs to be usable for statistic output and documentation.

Can anyone shed some light on this problem? I am thinking that MySQL must have a way to do it?

Am I speaking nonsense? I will be happy to elaborate further if needed.

cgraz

I assume you're using MySQL? Have a look at the manual

stormszero

I can't get ADDTIME working. Been thinking about it.

ADDTIME(column_name) doesn't work on it's own as I first hoped since it takes two arguments. It may be me who misread the somewhat thin MySQL manual. Could you explain how to get ADDTIME to calculate the "sum" of a column filled with hh:mm:ss (TIME)?

As far as i can see, ADDTIME can only be provided with two arguments:

arg1: TIME or DATETIME
arg2: TIME

bpat1434

If it's returning perfectly fine integers, why not just take the result, and start from the right and format it yourself:

<?php
format_time($string)
{
    // Make it an array
    $vals = str_split($string);

// Reverse it
   $vals = array_reverse($vals);

   // Format it (seconds & minutes should always be 2 digits)....
   $time = ':'.$vals[3].$vals[2].':'.$vals[1].$vals[0];
   unset($vals[0], $vals[1], $vals[2], $vals[3]);

   $hours = implode('', $vals);

   return $hours.$time;
}
?>

I'm sure there's an easier way, but something like that should work.... (I'm an idiot, I found the easier way....)

Alternatively, you could just use TIME_FORMAT() in the mySQL query to do it for you:

SELECT TIME_FORMAT(SUM(`ELAPSED_TIME`), '%H:%i:%s')

Hope that helps....

stormszero

bpat1434 - you pushed me in the right direction 🙂

SELECT TIME_FORMAT(SUM(time_elapsed), '%T')...

Resolved 🙂 Thanks a million for your help, guys!

bpat1434

No problem!!

Wait, just be careful. If you have a period that lasts longer than 24 hours, (i.e. the sum adds up to 25+) then your current code won't suffice. The %H will allow for larger numbers (see the entry for TIME_FORMAT in the MySQL manual) which is a possibility.