PHP/MySQL trying to get TIMESTAMPDIFF to output a result

Question

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!

Star_Man · Accepted Answer · 2017-01-30 19:00:11Z

1

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;

answered Jan 30, 2017 at 19:00

Star_Man

1,0911 gold badge14 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Corey Hart Over a year ago

Unfortunately I need this to work with mySQL. This is because I´ll be making a query that only SELECTs rows from a table, in which the timestamps are < x seconds old. Otherwise I should pull out data for each row and compare them using php, which would be very ineffecient. I know this is not obvious from the code I posted, but I wanted to keep my question as simple as I could, knowing that once I´d be able to get a result with TIMESTAMPDIFF, I´d also be able to proceed with the query.

Star_Man · Accepted Answer · 2017-01-30 18:54:28Z

0

mysql_query() function returns:

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.
For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.
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);

edited Jan 30, 2017 at 18:54

answered Jan 30, 2017 at 17:52

Star_Man

1,0911 gold badge14 silver badges33 bronze badges

3 Comments

Star_Man Over a year ago

@CoreyHart, Is the $result FALSE?

Corey Hart Over a year ago

No, there´s simply nothing being printf´ed at all.

Corey Hart Over a year ago

Still the same.

Collectives™ on Stack Overflow

PHP/MySQL trying to get TIMESTAMPDIFF to output a result

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related