[SOLVED] Get SUM from time

[dreamwest]

I need to tally the SUM from a column that has time format:

 

00:15

01:38

13:40

 

etc...

 

This works but when new time are added in seconds the sum of the column doesnt change.

 

$query = "SELECT SUM(time_length) FROM table"; 
$result = mysql_query($query) or die(mysql_error());

$row = mysql_fetch_array($result);
$total_minuets = $row['SUM(time_length)'] * 60;

 

Is it only getting the sum from the first 2 numbers - before the ":"

[PFMaBiSmAd]

That's because numbers are only parsed until a 'stop' character is found, a non-numeric character. In your case that is the : separator.

 

You cannot directly sum numbers that are made up of two different number bases minutes:seconds.

 

You need to use the mysql TIME_TO_SEC() function to convert to seconds, then perform the SUM(), then use the mysql SEC_TO_TIME() function to convert the result back to a time value.

[dreamwest]

Excellent. Works

 

Thanks

[vangelisb]

Hi thanks for the answer it worked for me to but i have a problem with it

if the time is above 838:59:59 then it stucks there

i know it is a common bug of mysql but do you kwon any solution for that ?

 

 

Thanks in advance

[fenway]

Well, you don't *need* mysql to convert it back from seconds to "time" -- you can do that quite easily yourself.

[vangelisb]

Thanks for the answer but because i am new to this can you please say how ?

 

Thanks

[fenway]

Thanks for the answer but because i am new to this can you please say how ?

That's now a php issue... but just keep dividing through.