Subtracting Time From Two Database Columns

[bschultz]

I need to subtract two times from a database.

 

<?php
$sql = "SELECT * FROM scoresheet WHERE period='1'";  
     	
$rs = mysql_query($sql,$dbc);  
$matches = 0; 




$start_time = $row[clock];

$end_time = $row[period_length];


    list($hours, $minutes, $seconds) = split(':', $start_time);

    $startTimestamp = mktime($hours, $minutes, $seconds);

    

    list($hours, $minutes, $seconds) = split(':', $end_time);

    $endTimestamp = mktime($hours, $minutes, $seconds);

    

    $seconds = $endTimestamp - $startTimestamp;

    $minutes = ($seconds / 60) % 60;

    $hours = floor($seconds / (60 * 60));

    

    $timepassed = "$hours:$minutes";




while ($row = mysql_fetch_assoc($rs))  {
$matches++; 

echo "$row[period]  $timepassed  $row[team]  $row[goal]  ($row[assist1]  $row[assist2])  $row[type]<br />";  

?>

 

 

This might be more of a php question...but since it involves a database, I put it in this forum. 

 

The two columns are both in the time format.

 

The value in the column period_length is 20:00:00

 

The value in the column clock is 16:15:00

 

So, the value after the math should be 3:45 (since I only want hours and minutes...not seconds)

 

Here's the error message I'm getting

 

Warning: mktime() expects parameter 1 to be long, string given in line 36

 

Warning: mktime() expects parameter 1 to be long, string given in line 42

 

The math also shows 0:0 instead of 3:45

 

Any ideas?

 

Thanks!

[rajivgonsalves]

you should use strtotime.

<?php

$first = "20:00:00";
$second = "16:15:00";

echo date("H:i", strtotime($first)-strtotime($second));
?>

 

however does your time go more than 24 hours ? that would mess up the calculation in hours

[bschultz]

Thanks for the help!

 

The "first" will never go higher than 20:00:00...

 

This...

<?php

$first = $row[period_length];  //which is 20:00:00 in the DB
$second = $row[clock];  //which is 16:15:00 in the DB


$timepassed = date("H:i", strtotime($first)-strtotime($second));



while ($row = mysql_fetch_assoc($rs))  {
$matches++; 

echo "$row[period]  $timepassed  $row[team]  $row[goal]  ($row[assist1]  $row[assist2])  $row[type]<br />";  
?>

 

echos 18:00 for $timepassed...which should be 3:45.

[rajivgonsalves]

what are the datatypes for those columns ?

[bschultz]

time

[akitchin]

this page (and the other ones in the "Functions and Operators" section) in the MySQL manual is well worth a look, because there are a lot of really handy functions that most people are unaware exist in MySQL:

 

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

 

in particular, have a look at TIMEDIFF().

[bschultz]

I get a blank result with this:

 

<?php

$sql = "SELECT * FROM scoresheet WHERE period='1'";  
     	
$rs = mysql_query($sql,$dbc);  
$matches = 0; 


while ($row = mysql_fetch_assoc($rs))  {
$matches++; 

$timepassed = mysql_query("select TIMEDIFF ('period_length' , 'clock')  FROM scoresheet");

echo "$row[period]  $timepassed  $row[team]  $row[goal]  ($row[assist1]  $row[assist2])  $row[type]<br />";  
}  
echo "";
?>

[fenway]

Then dump the row.

[bschultz]

how?

[premiso]

Ok, first up, your $timepassed should only return a Resource, if the query executed successfully. Second up, you are cacluclating the TIMEDIFF if a string value (the single quotes). Third, why not just calculate the timediff in the original query?

 

<?php
$sql = "SELECT period, team, goal, assist1, assist2, type, TIMEDIFF(`period_length`, `clock`) AS timepassed FROM scoresheet WHERE period='1'";  

$rs = mysql_query($sql,$dbc) or trigger_error("Query Failed: " . mysql_error());  
$matches = 0; 

while ($row = mysql_fetch_assoc($rs))  {
$matches++; 
echo "{$row['period'}]  {$row['timepassed']}  {$row['team']}  {$row['goal']}  ({$row['assist1']}  {$row['assist2']})  {$row['type']}<br />";  
}  
echo "";
?>

 

Give that a try, notice that I encapsulated the $row items in the echo with { and } and I added the single quotes for the associative indexes, this will cause less notice errors from PHP as just doing $row[index] causes a notice of undefined constant where as $row['index'] will not since that is how it should be done.

[akitchin]

note your use of single quotes here:

 

TIMEDIFF ('period_length' , 'clock')

 

this tells MySQL to treat "period_length" and "clock" as string literals, rather than their column names. and of course, it will falter on trying to compute the time difference between the strings "period_length" and "clock". the computer doesn't know that the answer should be roughly 42 seconds. thus premiso is using backticks (`) to surround the column names in his query, and alternatively, you can eliminate the quote delimiters altogether.

[bschultz]

Thanks for the help on this!  I got that part working...now I need to format the time to be just hours and minutes (no seconds).

 

So, I tried this:

 

<?php
$sql = "SELECT period, team, goal, assist1, assist2, type, TIMEDIFF(`period_length`, `clock`) AS timepassed, TIME_FORMAT(`timepassed`, `%k:%i`) AS timepassed2 FROM scoresheet WHERE period='1'";  
?>

 

and I get an error:

 

Unknown column 'timepassed' in 'field list'

[premiso]

TIME_FORMAT(TIMEDIFF(`period_length`, `clock`), '%k:%i') AS timepassed

 

Should work. No need to do a "timepassed2" as you can just format it like above.

[bschultz]

That did it...thanks a bunch!