How to Subtract current date time from date time i

Question

I'm working on a project.I have to check " how many minutes ago ,user updated the database " .For that I used following code :

<?php
include('connect_db.php');

$sql  =" SELECT * FROM test_table WHERE user='john' ORDER BY time_ DESC LIMIT 1" ;    

$result = $conn->query($sql);

if ($result->num_rows > 0) {

    
    while($row = $result->fetch_assoc()) {  

       $time = strtotime($row['time_']);

       $current=time();

       $sub_time=$current-$time;

     echo $sub_time;
}

The problem is the code is returning negative values like [ -17173 ]. The date-time stored in my db is like [ 2018-07-14 11:42:21.006515 ]

I simply want to compare current time with the the time stored in database get results in seconds or minutes . I need help to solve this issue.Thank you .

Answer 1

You first need to convert them to timestamp. And them subtract them convert the resuting timestamp back to format you want.

    while($row = $result->fetch_assoc()) {  

       $time = strtotime($row['time_']);

       $current=time();
       $sub_time=date('d-m-y h:i:s',(strototime(date('d-m-y h:i:s')-$time));
       echo $sub_time;
}

You need to handle the greater than and less than cases too.

Answer 2

You can just change your select statement to give you the time difference in seconds as shown below:

$sql  =" select UNIX_TIMESTAMP(current_Timestamp()) - UNIX_TIMESTAMP(time_) FROM test_table WHERE user='john' ORDER BY time_ DESC LIMIT 1" ; 

Answer 3

I think DateTime is better approach, as it has the methods to achieve the result you need.

maybe something like this:

$time=$row['time_'];
$curr_time=new DateTime();
$difference=$time->diff($curr_time);

http://php.net/manual/en/class.datetime.php

Answer 4

Updated

You should use the DateTime functions to figure out the difference in time. You should be able to integrate this into your code.

I incorporated my suggested code as a function into your original code. This should work for you.

Try this:

function timeDiff($date){

  $date     = new DateTime(date('Y-m-d H:i:s', strtotime($date)));
  $current  = new DateTime(date('Y-m-d H:i:s'));
  $interval = $date->diff($current);
  return $interval->format('%H:%I:%S');


}

include('connect_db.php');

$sql  ="SELECT * FROM test_table WHERE user='john' ORDER BY time_ DESC LIMIT 1" ;    

$result = $conn->query($sql);

  if ($result->num_rows > 0) {


      while($row = $result->fetch_assoc()) {  

         echo timeDiff($row['time_']);
  }

}