Jump to content

Timestamp difference - using subtraction between the two in the database


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

 

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

There is a popular library for enhancing the basic datetime api, called Carbon.  

One of the nice things that comes with it is "Human Diff".  See the manual:  https://carbon.nesbot.com/docs/#api-humandiff

  • Like 1

@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
  • Solution

The mysql function NOW() gives the current datetime.

mysql> SELECT timestampdiff(DAY, '2024-01-28 09:41:36', NOW() ) as diff;
+------+
| diff |
+------+
|    1 |
+------+

 

  • Like 1

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.