PHP/MySQL trying to get TIMESTAMPDIFF to output a

问题:

My intention with the below code is display the difference between two timestamps in seconds, but nothing is showing at all. I´ve added echo $ts for you to see what $ts contains:

$ts = $value['time'];
echo $ts;
$sql = "SELECT TIMESTAMPDIFF(SECOND,(CURRENT_TIMESTAMP),'$ts')";
$diff = mysql_query($sql);
echo $diff;

The result is:

2017-01-26 16:30:51

Hence it only shows the content of $ts, not what the time difference is. Help with understanding what I´m doing wrong would be appreciated, thanks!

回答1:

mysql_query() function returns:

1. For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

2. For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

3. The returned result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.

So you can try the following:

$con = mysql_connect("localhost", "root", "mypass") or
    die("Could not connect: " . mysql_error());
mysql_select_db("tutorials");//**your database name**//

$sql = "SELECT TIMESTAMPDIFF(SECOND,(CURRENT_TIMESTAMP),'$ts') as timestamp";
$result = mysql_query($sql);

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    printf("diff: %s", $row[0]);  
}

mysql_close($con);

回答2:

Using Php only.

$datetime1 = strtotime($ts);
$datetime2 = strtotime(date('Y-m-d H:i:s'));
$interval  = abs($datetime2 - $datetime1);
echo 'Diff. in seconds is: '.$interval;