[SOLVED] Adding times

[nuttycoder]

Hi I'm trying to figure out how to add times from from each row of a database and give a total time.

 

I have 2 columns in a table one called h which holds the hour and the second called m which holds the minutes.

 

I want to get all the hours and minutes from each row and add them to get a total time.

 

for instance I have this is my table:

  ID      h              m

1  03:00:00  00:10:00

2  04:00:00  00:10:00

3  01:00:00  00:19:00

 

so the final thing would be 08:39:00

 

Just not sure how to go about it.

[kickstart]

Hi

 

Not quite what you want, but assuming a table of 2 numeric columns, one for hours and one for mins (rather than you current setup which seems to be a char hh:mm:ss column for hours and another hh:mm:ss column for mins):-

 

SELECT CONCAT(FLOOR(totTime / (24*60)),":",FLOOR(MOD(totTime,(24*60))/60),":",MOD(totTime,60))
FROM(SELECT SUM(aTIME) totTime
FROM (SELECT ID, (h * 60) + m As aTime
FROM SomeTable) Deriv1) Deriv2

 

All the best

 

Keith

[nuttycoder]

SELECT CONCAT(FLOOR(totTime / (24*60)),":",FLOOR(MOD(totTime,(24*60))/60),":",MOD(totTime,60))
FROM(SELECT SUM(aTIME) totTime
FROM (SELECT ID, (h * 60) + m As aTime
FROM SomeTable) Deriv1) Deriv2

 

thank so if I change my columns to be numeric instead of dates, How would I use your code I mean in the code which parts refer to the column names? 

[kickstart]

Hi

 

Column names highlighted in red

 

SELECT CONCAT(FLOOR(totTime / (24*60)),":",FLOOR(MOD(totTime,(24*60))/60),":",MOD(totTime,60))

FROM(SELECT SUM(aTIME) totTime

FROM (SELECT ID, (h * 60) + m As aTime

FROM SomeTable) Deriv1) Deriv2

 

You could change this SQL to extract from you date / time fields if you have a reason to use date / time fields to just store either number of hours or number of minutes.

 

All the best

 

Keith

[nuttycoder]

Thanks I changed my columns to number (INT) then left the minutes at 0 for now then had 4 rows with different hours giving me a total of 10 hours, when I run the sql I get this back:

 

CONCAT(FLOOR(totTime / (24*60)),":",FLOOR(MOD(totTime,(24*60))/60),":",MOD(totTime,60))
0:10:0

 

Is this correct? as I would have though it would just give the hours and minutes.

 

What am trying to create is a simple form where a user enters the time spend on a job and can add multiple notes and then at the end it will show the total time say like 10:00:00.

[kickstart]

Hi

 

Format returned is Days, Hours and Minutes. Is that OK, or would you just like hours and mins?

 

All the best

 

Keith

[nuttycoder]

yeah if you could that would be great, then I'll have the option of doing it either way and see which is better.

 

thank you so much, I really should look at the more advanced mysql functions.

[nuttycoder]

trying to pull the result out with php and using the following code nothing appears on screen:

 

$sql = mysql_query(' SELECT CONCAT( FLOOR( totTime / ( 24 *60 ) ) , ":", FLOOR( MOD( totTime, ( 24 *60 ) ) /60 ) , ":", MOD( totTime, 60 ) )'
        . ' FROM ('
        . ' SELECT SUM( aTIME ) totTime'
        . ' FROM ('
        . ' SELECT ID, ('
        . ' h *60'
        . ' ) + m AS aTime'
        . ' FROM test'
        . ' )Deriv1'
        . ' )Deriv2 ')or die(mysql_error()); 
$t = mysql_fetch_object($sql);
echo $t->aTime;

 

But if I use print_r($t) I get this:

 

stdClass Object ( [CONCAT( FLOOR( totTime / ( 24 *60 ) ) , ":", FLOOR( MOD( totTime, ( 24 *60 ) ) /60 ) , ":", MOD( totTime, 60 ) )] => 2:11:15 )

 

So I guess am not pulling it from the database correctly, how would I print this out with php?

 

**EDIT**

 

manage to solve that by adding an AS aTime to the first query:

 

$sql = mysql_query(' SELECT CONCAT( FLOOR( totTime / ( 24 *60 ) ) , ":", FLOOR( MOD( totTime, ( 24 *60 ) ) /60 ) , ":", MOD( totTime, 60 ) ) AS aTime'
        . ' FROM ('
        . ' SELECT SUM( aTIME ) totTime'
        . ' FROM ('
        . ' SELECT ID, ('
        . ' h *60'
        . ' ) + m AS aTime'
        . ' FROM test'
        . ' )Deriv1'
        . ' )Deriv2 ')or die(mysql_error()); 
$t = mysql_fetch_object($sql);

[kickstart]

yeah if you could that would be great, then I'll have the option of doing it either way and see which is better.

 

Here you go:-

 

SELECT CONCAT(FLOOR(totTime/60),":",MOD(totTime,60)) as aTime
FROM(SELECT SUM(aTIME) totTime
FROM (SELECT ID, (h * 60) + m As aTime
FROM SomeTable) Deriv1) Deriv2

 

All the best

 

Keith