close but no cigar- subtracting the difference between two timestamp fields

[webguync]

I almost have this correct, but the output isn't what is should be for subtracting the difference between two timestamp fields. Here is my code

$login_time = strtotime($row['login_timestamp']);
$submit_time = strtotime($row['submit_timestamp']);
$completion_time = $login_time - $submit_time;
$completion_time /= 60;
$formatted_completion_time = floor($completion_time );
echo "<tr><th class='complete'>Completion Time:</th></tr><tr><td>";
echo $formatted_completion_time;
echo " minutes";
echo "</td></tr>";

 

-21248057 minutes is the output when it should be something like 3 minutes.

 

When I run SQL to determine the difference I get -00:02:28 which I don't understand either.

 

The SQL to get that is

SELECT TIMEDIFF('2010-05-26 10:13:53', '2010-05-26 10:16:21');

[MadTechie]

try this

SELECT TIMEDIFF( '2010-05-26 10:16:21', '2010-05-26 10:13:53' ) 

result: 00:02:28

[webguync]

ok, I see I just had the fields reversed, which is why I am getting the negative value.

 

In the PHP I provided though, I get a result of 21248056 minutes

 

not sure why this is being output if the result is 2 minutes and 28 seconds.

[kenrbnsn]

Using the OP's code (with a slight modification):

<?php
$row = array();
$row['login_timestamp'] = '2010-05-26 10:16:21';
$row['submit_timestamp'] = '2010-05-26 10:13:53';
$login_time = strtotime($row['login_timestamp']);
$submit_time = strtotime($row['submit_timestamp']);
$completion_time = $login_time - $submit_time;
$completion_time /= 60;
$formatted_completion_time = floor($completion_time );
echo "<tr><th class='complete'>Completion Time:</th></tr><tr><td>";
echo $formatted_completion_time;
echo " minutes";
echo "</td></tr>";
?>

I get

Completion Time:2 minutes

 

Ken

[MadTechie]

Personally I would do something like this

$SQL = "SELECT unix_timestamp(TIMEDIFF(submit_timestamp, login_timestamp)) as cTime"; //
//get mysql stuff

//some conditions if needed ie
if($row['cTime'] < 60){ //less than 1 minute show seconds
  $formatted_completion_time = date("s",$row['cTime'])." seconds"; 
}elseif($row['cTime'] < (3600)){
  $formatted_completion_time = date("i",floor($row['cTime']))." minutes";
}else{
  $formatted_completion_time = date("H",floor($row['cTime']))." hours"; //technically wrong as it won't show more than 23 hours but you get the idea
}

 

[webguync]

@medtechie that might work for what I need. I think part of my problem is the login_timestamp and submit_timestamp are in two different tables (Candidates and Responses respectively), and the user_id's weren't the same so the calculations are off.

 

Can I use this as the SQL?

$SQL = "SELECT unix_timestamp(TIMEDIFF(submit_timestamp, login_timestamp)) as cTime FROM Responses LEFT JOIN Candidates USING (user_id)";

 

also how would I echo out the results with this method?

 

thanks for all the help so far.

[webguync]

I have this code so far, but the SQL isn't working b/c nothing is being selected.

 

$sql_timestamp = "SELECT unix_timestamp(TIMEDIFF(submit_timestamp, login_timestamp)) as cTime FROM Responses LEFT JOIN Candidates USING (user_id)";

$result_timestamp = mysql_query($sql_timestamp);

if (!$result_timestamp) {
    echo "Could not successfully run query ($sql_timestamp) from DB: " . mysql_error();
    exit;
}
//some conditions if needed ie
if($row['cTime'] < 60){ //less than 1 minute show seconds
  $formatted_completion_time = date("s",$row['cTime'])." seconds"; 
}elseif($row['cTime'] < (3600)){
  $formatted_completion_time = date("i",floor($row['cTime']))." minutes";
}else{
  $formatted_completion_time = date("H",floor($row['cTime']))." hours"; //technically wrong as it won't show more than 23 hours but you get the idea
}
echo "<tr><th class='complete'>Completion Time:</th></tr><tr><td>";
echo $formatted_completion_time;
echo " minutes";
echo "</td></tr>";

 

also getting undefined index notices for cTime. This may also be doe to the fact that the SQL produces no results.

[mattal999]

I have this code so far, but the SQL isn't working b/c nothing is being selected.

 

$sql_timestamp = "SELECT unix_timestamp(TIMEDIFF(submit_timestamp, login_timestamp)) as cTime FROM Responses LEFT JOIN Candidates USING (user_id)";

$result_timestamp = mysql_query($sql_timestamp);

if (!$result_timestamp) {
    echo "Could not successfully run query ($sql_timestamp) from DB: " . mysql_error();
    exit;
}
//some conditions if needed ie
if($row['cTime'] < 60){ //less than 1 minute show seconds
  $formatted_completion_time = date("s",$row['cTime'])." seconds"; 
}elseif($row['cTime'] < (3600)){
  $formatted_completion_time = date("i",floor($row['cTime']))." minutes";
}else{
  $formatted_completion_time = date("H",floor($row['cTime']))." hours"; //technically wrong as it won't show more than 23 hours but you get the idea
}
echo "<tr><th class='complete'>Completion Time:</th></tr><tr><td>";
echo $formatted_completion_time;
echo " minutes";
echo "</td></tr>";

 

also getting undefined index notices for cTime. This may also be doe to the fact that the SQL produces no results.

You don't use $row = mysql_fetch_array($result_timestamp); before you start the comparison checks. Put that statement just after the mysql_query part.

[webguync]

thanks, but I don't see the line being referred to.

[MadTechie]

See ADDED

$sql_timestamp = "SELECT unix_timestamp(TIMEDIFF(submit_timestamp, login_timestamp)) as cTime FROM Responses LEFT JOIN Candidates USING (user_id)";

$result_timestamp = mysql_query($sql_timestamp);
if (!$result_timestamp) {
    echo "Could not successfully run query ($sql_timestamp) from DB: " . mysql_error();
    exit;
}
$row = mysql_fetch_assoc($result_timestamp); //<----ADDED

//some conditions if needed ie
if($row['cTime'] < 60){ //less than 1 minute show seconds
  $formatted_completion_time = date("s",$row['cTime'])." seconds"; 
}elseif($row['cTime'] < (3600)){
  $formatted_completion_time = date("i",floor($row['cTime']))." minutes";
}else{
  $formatted_completion_time = date("H",floor($row['cTime']))." hours"; //technically wrong as it won't show more than 23 hours but you get the idea
}
echo "<tr><th class='complete'>Completion Time:</th></tr><tr><td>";
echo $formatted_completion_time;
echo " minutes";
echo "</td></tr>";

[webguync]

thanks, I think the code will work ok, but my SQL produced

 

cTime

0

0

 

which of course produces zero in my output. No errors or anything. Any ideas on why the zeros are being produced?

[webguync]

as an amendment I noticed when I take unix_timestamp out of the SQL and run it through PHPMyAdmin I get non zero values.

cTime
-00:53:28
-01:56:18

 

but in the PHP I get the following warning.

 

Notice: A non well formed numeric value encountered in /path_to_file/Results.php  on line 77

 

 

 

[MadTechie]

Hi webguync,

Let get this solved

Here are 2 methods,

 

Minutes only

<?php
$sql_timestamp = "SELECT TIMESTAMPDIFF(MINUTE,submit_timestamp,login_timestamp) as cTime FROM Responses LEFT JOIN Candidates USING (user_id)";

$result_timestamp = mysql_query($sql_timestamp);
if (!$result_timestamp) {
    echo "Could not successfully run query ($sql_timestamp) from DB: " . mysql_error();
    exit;
}
while($row = mysql_fetch_assoc($result_timestamp)){
  echo "<tr><th class='complete'>Completion Time:</th></tr><tr><td>\n";
  echo $row['cTime']." Minutes";
  echo "</td></tr>";
}
?>

 

Expanded

<?php
$sql_timestamp = "SELECT TIMESTAMPDIFF(SECOND,submit_timestamp,login_timestamp) as cTime FROM Responses LEFT JOIN Candidates USING (user_id)";

$result_timestamp = mysql_query($sql_timestamp);
if (!$result_timestamp) {
    echo "Could not successfully run query ($sql_timestamp) from DB: " . mysql_error();
    exit;
}
while($row = mysql_fetch_assoc($result_timestamp)){
  $formatted_completion_time = formatTime($row['cTime']);
  echo "<tr><th class='complete'>Completion Time:</th></tr><tr><td>\n";
  echo $formatted_completion_time;
  echo "</td></tr>";
}

function formatTime($cTime){
  if($cTime < 60){ //less than 1 minute show seconds
    $formatted_completion_time = date("s",$cTime)." seconds";
  }elseif($cTime < 3600){ //1 hour
    $formatted_completion_time = date("i",$cTime)." minutes";
  }elseif($cTime < 86400){ //24 hours
    $formatted_completion_time = date("H:i",$cTime)." hours";
  }else{
    $formatted_completion_time = round($cTime/86400,0)." days"; 
  }
  return $formatted_completion_time;
}
?>

 

Hope they help

[webguync]

thanks again for the help. I think I am getting close, but using the expanded code you provided I get the following error.

 

"

Fatal error: Call to undefined function formatTime() in /path_to_file/Results.php on line 74"

[mrMarcus]

make sure the function formatTime() is within scope of the code/script calling it.

[Andy-H]

<?php

function formatTime($cTime){
  if($cTime < 60){ //less than 1 minute show seconds
    $formatted_completion_time = date("s",$cTime)." seconds";
  }elseif($cTime < 3600){ //1 hour
    $formatted_completion_time = date("i",$cTime)." minutes";
  }elseif($cTime < 86400){ //24 hours
    $formatted_completion_time = date("H:i",$cTime)." hours";
  }else{
    $formatted_completion_time = round($cTime/86400,0)." days"; 
  }
  return $formatted_completion_time;
}

$sql_timestamp = "SELECT TIMESTAMPDIFF(SECOND,submit_timestamp,login_timestamp) as cTime FROM Responses LEFT JOIN Candidates USING (user_id)";

$result_timestamp = mysql_query($sql_timestamp);
if (!$result_timestamp) {
    echo "Could not successfully run query ($sql_timestamp) from DB: " . mysql_error();
    exit;
}
while($row = mysql_fetch_assoc($result_timestamp)){
  $formatted_completion_time = formatTime($row['cTime']);
  echo "<tr><th class='complete'>Completion Time:</th></tr><tr><td>\n";
  echo $formatted_completion_time;
  echo "</td></tr>";
}
?>

[webguync]

now I get this error.

 

"Fatal error: Cannot redeclare formattime() (previously declared in /path_to_file/Results.php  on line 66"

[MadTechie]

That means you have the function their twice!

can you post what you have

[webguync]

function formatTime($cTime){
  if($cTime < 60){ //less than 1 minute show seconds
    $formatted_completion_time = date("s",$cTime)." seconds";
  }elseif($cTime < 3600){ //1 hour
    $formatted_completion_time = date("i",$cTime)." minutes";
  }elseif($cTime < 86400){ //24 hours
    $formatted_completion_time = date("H:i",$cTime)." hours";
  }else{
    $formatted_completion_time = round($cTime/86400,0)." days"; 
  }
  return $formatted_completion_time;
}

$sql_timestamp = "SELECT TIMESTAMPDIFF(SECOND,submit_timestamp,login_timestamp) as cTime FROM Responses LEFT JOIN Editor_Candidates USING (user_id)";

$result_timestamp = mysql_query($sql_timestamp);
if (!$result_timestamp) {
    echo "Could not successfully run query ($sql_timestamp) from DB: " . mysql_error();
    exit;
}
while($row = mysql_fetch_assoc($result_timestamp)){
  $formatted_completion_time = formatTime($row['cTime']);
  echo "<tr><th class='complete'>Completion Time:</th></tr><tr><td>\n";
  echo $formatted_completion_time;
  echo "</td></tr>";
}

[mrMarcus]

well, there isn't 66 lines of code there, so you're obviously leaving chunks out.  no need to beat around the bush, just post all your code (within reason).

[webguync]

oh sorry, this is the PHP portion.

 

<?php

$conn = mysql_connect("localhost","username","pw");

if (!$conn) {
    echo "Unable to connect to DB: " . mysql_error();
    exit;
}
  
if (!mysql_select_db("ETSI_Internal")) {
    echo "Unable to select mydbname: " . mysql_error();
    exit;
}

$sql = "SELECT Responses.editor_name,Answer1,Answer2,Answer3,Answer4,Answer5,Answer6,Answer7,Answer8,Answer9,Answer10,Answer11,Answer12
        FROM Responses ";
       

$result = mysql_query($sql);



if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

// While a row of data exists, put that row in $row as an associative array
// Note: If you're expecting just one row, no need to use a loop
// Note: If you put extract($row); inside the following loop, you'll
//       then create $userid, $fullname, and $userstatus
while ($row = mysql_fetch_assoc($result)) {
   echo "<tr><td class='name'>{$row['editor_name']}</td></tr>";
echo "<tr><td class='section'><strong>Section 1</strong></td></tr>";
   
   for ($i =1;$i<9;++$i) {
echo "<tr><td>{$row['Answer'.$i]}</td></tr>";
  }
echo "<tr><td class='section'><strong>Section 2</strong></td></tr>";

echo "<tr><td>{$row['Answer10']}</td></tr>";
echo "<tr><td class='section'><strong>Section 3</strong></td></tr>";

echo "<tr><td>{$row['Answer11']}</td></tr>";
echo "<tr><td class='section'><strong>Section 4</strong></td></tr>";

echo "<tr><td>{$row['Answer12']}</td></tr>";

function formatTime($cTime){
  if($cTime < 60){ //less than 1 minute show seconds
    $formatted_completion_time = date("s",$cTime)." seconds";
  }elseif($cTime < 3600){ //1 hour
    $formatted_completion_time = date("i",$cTime)." minutes";
  }elseif($cTime < 86400){ //24 hours
    $formatted_completion_time = date("H:i",$cTime)." hours";
  }else{
    $formatted_completion_time = round($cTime/86400,0)." days"; 
  }
  return $formatted_completion_time;
}

$sql_timestamp = "SELECT TIMESTAMPDIFF(SECOND,submit_timestamp,login_timestamp) as cTime FROM Responses LEFT JOIN Candidates USING (user_id)";

$result_timestamp = mysql_query($sql_timestamp);
if (!$result_timestamp) {
    echo "Could not successfully run query ($sql_timestamp) from DB: " . mysql_error();
    exit;
}
while($row = mysql_fetch_assoc($result_timestamp)){
  $formatted_completion_time = formatTime($row['cTime']);
  echo "<tr><th class='complete'>Completion Time:</th></tr><tr><td>\n";
  echo $formatted_completion_time;
  echo "</td></tr>";
}
}
mysql_free_result($result);



?>

[mrMarcus]

formatTime() is being declared within a while() loop.  With every iteration of the loop, formatTime() is being redeclared (and is obviously throwing a fatal error).

 

rule of thumb:  create a file called functions.php and include it at the top of your script(s).  This way, your function(s) can be used throughout any script you have without having to add it on every script.

 

to sum it up, take formatTime() out of the while loop and you'll be fine.

[webguync]

thanks for the tip. OK, I added the function as an include at the top of the page, but still getting some weirdness with the results. I have two records I am displaying and I am getting two results for Completion time, and they are EXACTLY the same for both records.

 

Completion Time:

53 minutes

Completion Time:

20:56 hours

 

current code

 

<html>
<head>
<title>Exam Results</title


</head>
<body>

<?php include("includes/functions.php"); ?>
<h1 class="results">exam results</h1>
<table id="results">
<tr><th>Candidate Name</th></tr>

<?php
ini_set("display_errors","1");
ERROR_REPORTING(E_ALL);
$conn = mysql_connect("localhost","uname","pw");

if (!$conn) {
    echo "Unable to connect to DB: " . mysql_error();
    exit;
}
  
if (!mysql_select_db("MyDB")) {
    echo "Unable to select mydbname: " . mysql_error();
    exit;
}

$sql = "SELECT Responses.name,Answer1,Answer2,Answer3,Answer4,Answer5,Answer6,Answer7,Answer8,Answer9,Answer10,Answer11,Answer12
        FROM Responses ";
       

$result = mysql_query($sql);



if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}


while ($row = mysql_fetch_assoc($result)) {
   echo "<tr><td class='name'>{$row['name']}</td></tr>";
echo "<tr><td class='section'><strong>Section 1</strong></td></tr>";
   
   for ($i =1;$i<9;++$i) {
echo "<tr><td>{$row['Answer'.$i]}</td></tr>";
  }
echo "<tr><td class='section'><strong>Section 2</strong></td></tr>";

echo "<tr><td>{$row['Answer10']}</td></tr>";
echo "<tr><td class='section'><strong>Section 3</strong></td></tr>";

echo "<tr><td>{$row['Answer11']}</td></tr>";
echo "<tr><td class='section'><strong>Section 4</strong></td></tr>";

echo "<tr><td>{$row['Answer12']}</td></tr>";



$sql_timestamp = "SELECT TIMESTAMPDIFF(SECOND,submit_timestamp,login_timestamp) as cTime FROM Responses LEFT JOIN Candidates USING (user_id)";

$result_timestamp = mysql_query($sql_timestamp);
if (!$result_timestamp) {
    echo "Could not successfully run query ($sql_timestamp) from DB: " . mysql_error();
    exit;
}
while($row = mysql_fetch_assoc($result_timestamp)){
  $formatted_completion_time = formatTime($row['cTime']);
  echo "<tr><th class='complete'>Completion Time:</th></tr><tr><td>\n";
  echo $formatted_completion_time;
  echo "</td></tr>";
}

}
mysql_free_result($result);



?>

</table>
</body>
</html>

 

in the function include

 

<?php
function formatTime($cTime){
  if($cTime < 60){ //less than 1 minute show seconds
    $formatted_completion_time = date("s",$cTime)." seconds";
  }elseif($cTime < 3600){ //1 hour
    $formatted_completion_time = date("i",$cTime)." minutes";
  }elseif($cTime < 86400){ //24 hours
    $formatted_completion_time = date("H:i",$cTime)." hours";
  }else{
    $formatted_completion_time = round($cTime/86400,0)." days"; 
  }
  return $formatted_completion_time;
}
?>

 

the first records has timestamps of [2010-05-17 12:26:13,2010-05-17 11:32:45] and the second record [2010-05-25 18:44:52,2010-05-25 16:48:34]

[webguync]

I know what is happening with this, just don't know why.  The completion time is appearing every time below a persons info, for example in the first part of my script I am pulling the name, answers from the DB and displaying that.

 

so what I am getting is...

 

Tom Thumb

Answer 1

Answer 2

Answer 3 etc.

[time 1]

[time 2]

[time 3]

 

Sally Smith

Answer 1

Answer 2

Answer 3 etc.

[time 1]

[time 2]

[time 3]

 

Bob Jones

Answer 1

Answer 2

Answer 3

[time 1]

[time 2]

[time 3]

 

also a time that should be displaying as one minute is displaying as 20:56 hours (?)

[MadTechie]

change

echo $formatted_completion_time;

to

echo ($row['cTime']/60)." minutes AKA ".$formatted_completion_time;

and see what you get

 

EDIT: if the minutes are wrong then the query is wrong