Jump to content

Timestamp difference - using subtraction between the two in the database


PNewCode
Go to solution Solved by Barand,

Recommended Posts

Hello. I have 2 columns called "visited" and "created_at"
I want to get the time between the two (visited date changes each time a person visits their login)

So the goal is to echo, for example

Created At
Jan 27 2024 09:41AM
------

Visited At
Jan 29 2024 09:41AM

Last visit was 2 days ago



I can't seem to get this right. Any help is appreciated.
This is what I have so far

$date1 = $row["date_format(created_at, '%b %e %Y %h:%i%p')"];
$date2 = $row["date_format(visited, '%b %e %Y %h:%i%p')"];
$datediff = $date1 - $date2;

echo "<b>$datediff</b>";

However, the following will echo them if I have it like the following, though thats each date and no subtraction

echo '.$row["date_format(visited, '%b %e %Y %h:%i%p')"].';

echo '.$row["date_format(created_at, '%b %e %Y %h:%i%p')"].';

Also I have this 

$sql = "SELECT date_format(visited, '%b %e %Y %h:%i%p'),date_format(created_at, '%b %e %Y %h:%i%p')";

 

Link to comment
Share on other sites

PHP

$created = new DateTime('2024-01-27 09:41:00');
$visited = new DateTime('2024-01-29 09:41:00');

echo $visited->diff($created)->format('%a days');          // 2 days

SQL

SELECT created
     , visited
     , timestampdiff(DAY, created, visited) as days
FROM test_1;

+---------------------+---------------------+------+
| created             | visited             | days |
+---------------------+---------------------+------+
| 2024-01-27 09:41:00 | 2024-01-29 09:41:00 |    2 |
+---------------------+---------------------+------+

 

  • Like 1
Link to comment
Share on other sites

@Barand thank you. Can you tell me how I would subtract the current from the value in the database. For example

Current time is 2024-01-29 09:41:36
and the column "Visited" is 2024-01-28 09:41:36

Then I would echo "Last Visited 1 Day Ago"

Edited by PNewCode
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.