如何创建计算上次看到的时间的系统

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?

Answer 1

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.