Calculating the difference between two timestamps

[smithmr8]

Hi,

I'm trying to calculate the time difference between two timestamps.

The two values are stored at timestamps in an SQL table. The code I'm currently using is :

 

<?

$ticket = mysql_query("SELECT * FROM `ticket` ORDER BY ID ASC LIMIT 1");
while ($t = mysql_fetch_array($ticket)){
$reply = mysql_query("SELECT * FROM `response` WHERE `ticket_id`=$t[iD] ORDER BY ID ASC");
$r = mysql_fetch_array($reply);
echo $t['timestamp']."<br>";
echo $r['timestamp']."<br>";

if($t['timestamp'] >= $r['timestamp']){
$difference = $t['timestamp'] - $r['timestamp'];
} else {
$difference = $r['timestamp'] - $t['timestamp'];
}
echo $difference;
}

?>

 

This isn't working though. The two timestamps being using in this example are :

 

2009-04-18 05:55:30

2009-04-19 13:37:00

 

Help would be very much appreciated.

 

Regards,

Luke

[Mchl]

<?php
$sql = "SELECT SUBTIME(r.timestamp, t.timestamp) AS timeDifference FROM ticket AS t INNER JOIN response AS r ON (t.ID = r.ticket_ID)";
$result = mysql_query($sql) or die(mysql_error() . ": $sql");
$row = mysql_fetch_array($result);
echo $row['timeDifference'];

 

 

[premiso]

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

 

<?php
$sql = "SELECT DATEDIFF(r.timestamp, t.timestamp) AS timeDifference FROM ticket AS t INNER JOIN response AS r ON (t.ID = r.ticket_ID) ORDER BY t.ID ASC";
$result = mysql_query($sql) or die(mysql_error() . ": $sql");
$difference = mysql_result($result, 0, 0);
echo $difference;

 

EDIT:

Well I trashed mine and just changed mchl's to use mysql_result since we are only grabbing 1 column from 1 row And to use DATEDIFF

 

[Mchl]

How did you guess we're calculating dates only, I do not know I would hope that a response to my ticket will come within hours, not days

[premiso]

How did you guess we're calculating dates only, I do not know I would hope that a response to my ticket will come within hours, not days

 

lol. Yea, that is true. I guess it never really clicked till now it is "DATEDIFF" not "TIMEDIFF"

 

*ashamed* goes and crawls back into his corner in fetal position muttering random shit to himself.

[Mchl]

Hey, come on! At least you ordered the query results!

[premiso]

Hey, come on! At least you ordered the query results!

 

True, and we both forgot to add the LIMIT 1 to the query....

 

Wow we are off.

 

$sql = "SELECT SUBTIME(r.timestamp, t.timestamp) AS timeDifference FROM ticket AS t INNER JOIN response AS r ON (t.ID = r.ticket_ID) ORDER BY t.ID ASC LIMIT 1";

[smithmr8]

Hi,

Thanks for the responses. Where about would I put that code ? Do I put it somewhere inside the existing code I had, or do I replace that old with this new ?

[Mchl]

Hey smithmr8! ya hear? We 're jumping to each other throats to help you! Hope we're not in vain

 

[edit]

 

That's basically a total rewrite of your code, so you put it instead of your original one.

[premiso]

The code that we provided is to replace your existing code...is it really not that obvious?

 

Remove all the code you just posted here in the file.

 

Replace it with this:

<?php
$sql = "SELECT SUBTIME(r.timestamp, t.timestamp) AS timeDifference FROM ticket AS t INNER JOIN response AS r ON (t.ID = r.ticket_ID) ORDER BY t.ID ASC LIMIT 1";
$result = mysql_query($sql) or die(mysql_error() . ": $sql");
$difference = mysql_result($result, 0, 0);
echo $difference;
?>

 

That is equivalent to the code you wrote, just simplified and written correctly.

[smithmr8]

Hehe .

Have replaced my code with that code, yet its not showing anything. $difference seems to have no value.

[premiso]

I think the query has an error.

 

$sql = "SELECT SUBTIME(r.timestamp, t.timestamp) AS timeDifference FROM ticket t INNER JOIN response r ON (t.ID = r.ticket_ID) ORDER BY t.ID ASC LIMIT 1";

 

I do not think that AS can be used to make an alias of a table name. Replace it with that and see if it works.

 

If not something else is going on....what it is my super powers cannot see.

[Mchl]

I do not think that AS can be used to make an alias of a table name. Replace it with that and see if it works.

 

I think we would be treated to an error message if it couldn't More likely we didn't guess the relations between the data in both tables correctly.

[smithmr8]

Replaced, but still nothing. .

[premiso]

I think we would be treated to an error message if it couldn't More likely we didn't guess the relations between the data in both tables correctly.

 

I would think so too...but you never know.

 

Maybe I am just too used to doing SQL in Mysql < 4 versions (or Oracle 9i). As I know you cannot use AS for the table names in one of those I remember griping about that how it was stupid to not allow it.

 

More likely we didn't guess the relations between the data in both tables correctly

 

Try this:

$sql = "SELECT SUBTIME(r.timestamp, t.timestamp) AS timeDifference FROM ticket t, response r WHERE t.ID = r.ticket_ID ORDER BY t.ID ASC LIMIT 1";

 

I have never been good with joins, but that seems to generally work for me Also since we do not know the exact structure, like mchl said, maybe the inner join was causing an issue....

 

But nevertheless my take on the issue now is just about all guess work. There are no error messages displaying?

[smithmr8]

Replaced it. I forgot I'd turned errors off.

 

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 52 in /home/bytefish/public_html/support/stats.php on line 6

 

 

[Mchl]

Hey premiso: that's your function, isn't it?

[premiso]

Are you sure you have data in your table? It seems like you are missing it.

 

<?php
$sql = "SELECT SUBTIME(r.timestamp, t.timestamp) AS timeDifference FROM ticket AS t INNER JOIN response AS r ON (t.ID = r.ticket_ID) ORDER BY t.ID ASC LIMIT 1";
$result = mysql_query($sql) or die(mysql_error() . ": $sql");

if (mysql_num_rows($result) > 0) {
    $difference = mysql_result($result, 0, 0);
    echo $difference;
}else {
    echo "Your query returned not results. Sorry.";
}
?>

 

If that echos "Your query returned no results..." then the issue lies within the data inside your database. Are you sure that there is a r.ticket_ID that matches a t.ID in your database at the moment?

 

Hey premiso: that's your function, isn't it?

 

Yep. It is just causing an issue cause no rows were returned.

[Mchl]

I never used it actually. Got no idea how it's indexed.

[smithmr8]

Ok. I forgot I'd emptied the tables.. so both tables now have data in.

Replaced the code, and nothing is displaying. No errors, and no message.

[premiso]

I never used it actually. Got no idea how it's indexed.

 

I only use it for COUNT queries or queries I only want 1 column returned from 1 row. Just better than assigning an array then the array to a value (granted I would just use the array) but yea. Most people tend to want to convert it to a value.

 

It can be handy

 

Replaced the code, and nothing is displaying. No errors, and no message.

 

Dunno what to say. It should be retrieving the difference, maybe try using mysql_fetch_assoc instead of the mysql_result and see if that works.

 

Given that you do not see an error message it seems that there were rows returned.

 

Try this:

<?php
$sql = "SELECT SUBTIME(r.timestamp, t.timestamp) AS timeDifference FROM ticket AS t INNER JOIN response AS r ON (t.ID = r.ticket_ID) ORDER BY t.ID ASC LIMIT 1";
$result = mysql_query($sql) or die(mysql_error() . ": $sql");

if (mysql_num_rows($result) > 0) {
    $difference = mysql_fetch_assoc($result);
    echo "The difference is: " . $difference['timeDifference'];
}else {
    echo "Your query returned not results. Sorry.";
}
?>

 

If the "The Difference is" is displayed, then I am not sure what is going on. And there is really no way for us to diagnose this issue for you as it could be as simple as there is no connecting ID's in your table. Make sure you have an id that matches the response and ticket field. If you do not this will obviously not return any records. 

[Mchl]

Why don't you show us an example of data from your tables?

[smithmr8]

Nope. Still nothing. Thanks for trying, and being patient, but for such a small item its more hassle than its worth. .

Thanks Anyway. =]