How to Subtract current date time from date time in database - PHP

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 .

Your code seems fine, but I'm not sure `strtotime()` can understand your date-time format. The PHP manual is not very helpful: http://php.net/manual/en/datetime.formats.compound.php I don't see yours there. Why not cut the last bit off, and use the MySQL format? So you're left with this bit: `2018-07-14 11:42:21`. — KIKO Software, Jul 14 '18 at 08:17
The time() function returns the date in UTC. If your database time is in local time, use date() instead. — slaakso, Jul 14 '18 at 08:23
https://stackoverflow.com/questions/10907750/calculate-difference-between-two-datetimes-in-mysql — Casimir et Hippolyte, Jul 14 '18 at 08:24

score 2 · Answer 1 · answered Jul 14 '18 at 08:25

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" ;

answered Jul 14 '18 at 08:25

Ankur Patel

1,413
8
14

score 0 · Answer 2 · answered Jul 14 '18 at 08:25

0

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

answered Jul 14 '18 at 08:25

domjanzsoo

81
2
9

Joseph_J · Answer 3 · 2018-07-14T08:45:38.160

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_']);
  }

}

Faizan Fayaz · Answer 4 · 2018-07-14T08:27:21.147

-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.

edited Jul 14 '18 at 08:27

answered Jul 14 '18 at 08:21

Faizan Fayaz

540
5
16

How to Subtract current date time from date time in database - PHP

4 Answers4

Linked

Related