comparing time values to find elapsed time...

[Les Mackenzie]

I'm in the process of writing a script that has turned into a bit of a mess...  Here's what I'm trying to accomplish...

I have two timestamp values stored in 'my' mysql database logged and last_updated.  I want to select all the values for a specific user, compare the values (time elapsed between the two)  create an average and then display it in a readable format.

How do you compare timestamps for an operation like this?

I have no idea how to do this but here is what I have so far...


<?
$query_time = "SELECT * FROM $table_one WHERE assoc_id = $_SESSION[USER_ID] AND status = '3'";
$result_time = mysql_db_query($database, $query_time) or die ("Error in query: $query_time.".mysql_error());
?>
<table border="1" width="100%" id="table3">
<?
while ($a_row = mysql_fetch_assoc($result_time))
{
$time_lapse =($a_row['last_updated'] - $a_row['logged']);
$logged = time_format($time_lapse);

?>
<tr>
<td><? echo $logged; ?></td>
</tr>
<?
}
?>
</table>


Can anyone help me solve this problem or steer me in the right direction?

[Oldiesmann]

Could you be a bit more specific? There are 12 seperate log tables in SMF.

[Les Mackenzie]

it's a general question...  not SMF related.  As you can see by the slopiness this is my code not [Unknown]'s

[Oldiesmann]

I realize that's your code, but didn't realize it wasn't SMF related...

I think that the strftime function would be useful for you. Check out http://www.php.net/manual/en/function.strftime.php

[Les Mackenzie]

It's not really a formatting question but a comparing question...  for instance I'm subtracting one value from the other and in some cases the value returned is not 14 digits in length.  Is there another way to compare two timestamps and get the proper time elapsed?

[Oldiesmann]

I see what you're saying. The lengthy string of digits is the number of seconds since the Unix epoch (whatever that is), which is January 1st, 1970 0:00:00 GMT (or December 31st, 1969 7:00:00PM Eastern time). So, by subtracting the two values, you're getting the difference in seconds.

EDIT: Here's something that will set $logged to display the difference in HH:MM:SS format...

$hours = $time_lapse / 3600; //Determine how many hours
$time_lapse -=($hours * 3600); // Subtract 3600 seconds for each hour
$minutes = $time_lapse / 60; //Determine the number of minutes
$time_lapse -= ($minutes * 60); // Subtract 60 seconds for each minute
$seconds = $time_lapse; //The rest is the number of seconds

//Format the minutes and seconds
$minutes = $minutes > 9 ? $minutes : '0' . $minutes
$seconds = $seconds > 9 ? $seconds : '0' . $seconds

$logged = $hours . ':' . $minutes . ':' . $seconds;

That will display it the way you want it (if hours are less than 10, then it will only display 1 digit instead of 2).

[Les Mackenzie]

Thanks for all the help Oldiesmann.

But I'm feeling like a n00b...  here is an example of a value that is returned by the comparison... 7004127.  Now when I use the script you gave me to format it the value echo's back 1945.5908333333:00:00.  Am I doing something wrong here?

[Oldiesmann]

See my response to your post in the help wanted section.