How can I create a system that calculates the time last seen

Question

I'm trying to show my website's users a time/date (which determines the last time of their visit of my website) in their profile page. Something exactly like this:

Well how can I calculate that number (8 min ago) ?

Currently I have a table like this:

// requests
+----+----------+-------------+
| id | id_user  |  unix_time  |
+----+----------+-------------+
| 1  | 2353     | 1339412843  |
| 2  | 2353     | 1339412864  |
| 3  | 5462     | 1339412894  |
| 4  | 3422     | 1339412899  |
| 5  | 3422     | 1339412906  |
| 6  | 2353     | 1339412906  |
| 7  | 7785     | 1339412951  |
| 8  | 2353     | 1339413640  |
| 9  | 5462     | 1339413621  |
| 10 | 5462     | 1339414490  |
| 11 | 2353     | 1339414923  |
| 12 | 2353     | 1339419901  |
| 13 | 8007     | 1339424860  |
| 14 | 7785     | 1339424822  |
| 15 | 2353     | 1339424902  |
+----+----------+-------------+

As you see table above stores all my website's requests. I mean I insert a new row into that table when user opens (loads) any page of my website. And then to calculate last_seen, I simply select last row for that specific user and use him unix_time value.

To be honest, I guess what I'm doing is wrong (or at least isn't standard). Because there is a lot of costs to calculate just a last_seen.

Also some days ago, I saw a file named access_log.txt in the server which was containing all requests. Now I want to know can I use that file for doing that? Also would it be better than my current approach (using database) ? In total, how can I calculate last_seen for each user?

something like this http://stackoverflow.com/questions/11/how-can-relative-time-be-calculated-in-c — Abhishek Gurjar, Jun 23 '16 at 13:12
Are you asking about providing the "last seen" on a page load? Or "continually", as happens in various ways on this forum ('asked', 'number of upvotes', etc)? The latter doubtless involves Javascript. — Rick James, Jun 28 '16 at 18:05

cb0 · Accepted Answer · 2016-06-23T13:28:00.490

Just use Datetime::diff, get the timestamp from the last entry in your database for user X. Then create 2 datetime objects, the first one with your old code and the second one with the current time. Then call diff and hand in the current timestamp.

$datetime1 = new DateTime(date('Y-m-d H:i:s', 1339412843));
$datetime2 = new DateTime(date('Y-m-d H:i:s'));

$diff = $datetime1->diff($datetime2);

echo $diff->y.' Years <br/>';
echo $diff->m.' Months <br/>';
echo $diff->d.' Days <br/>';
echo $diff->h.' Hours <br/>';
echo $diff->i.' Minutes <br/>';
echo $diff->s.' Seconds <br/>';

Appendix: Your current approach is correct. Calculating the time diff in mysql is not as costly as parsing the access logs. Using sql this is 1 query execution, 2 objects to be created and at least 1 function call.

If you want to use access logs you always have to parse the full access log. Parse all lines and then filter out unrelated lines and only keep the last on in which the user logged in. If your apache has already rotated the logs you will also have to search in other logs that you also have to uncompress.

I would keep your current strategy and try to optimize it e.g.

Using joins to get all users and all "last" timestamps at once that are visible on the current page.
Do not include "seconds seen before" so you could use some kind of caching.

Well thanks .. but actually I can calculate the difference between two *unix_time*. Now I'm trying to understand should I store all user's requests into the database? Or should I use that `log.txt` file? Or there is a bettern approach than them? So my question is somehow about the algorithm. — stack, Jun 23 '16 at 13:16
Ok, I have extenden my answer. Basically I think your approch is correct! :) — cb0, Jun 23 '16 at 13:28

score 1 · Answer 2 · answered Jun 28 '16 at 18:08

Depending on the quantity of activity (think about this forum!), you may want a "push" model instead of a "pull" model.

"Pull": When the user loads a page, fetch some dates from the database and compute the "last seen".

"Push": Keep track of who is watching for the info. Then, when something changes, "push" the info to the listeners. This would involve extra tables and extra code to orchestrate the "push".

How can I create a system that calculates the time last seen

2 Answers2