MySQL和PHP存储并比较时间戳

Question

Currently I have a table in my database to store information related to each user, with each row represents a user. I hope to add two columns to it, one stores the most recent time when the user logs in, the other stores the most recent time when the user logs out.

1. As I'm relatively new to MySQL and PHP, I wonder what's the easiest way to store the current time in MySQL? Every time when a user logs in or logs out, I want to update the corresponding login/logout time column with the current time.

2. Can I compare the two timestamps using the method describe in question 1? I want to compare the last log in time with the last log out time in order to determine whether a user is currently logged in or not.

Thank you.

Answer 1

You should set the columns type as datetime and update those columns with the NOW() function when the user login/logout:

update users set login_datetime = now() where user_id = :userId

update users set logout_datetime = now() where user_id = :userId

In PHP you can easily take those columns and compare them or do whatever you like by using the DateTime object:

$userLoggedInAt = new DateTime($row['login_datetime']);
$userLoggedOutAt = new DateTime($row['logout_datetime']);

$loginTime = $userLoggedOutAt->diff($userLoggedInAt);

echo 'The user was logged in for ' . $loginTime->format('%i minutes');

Although I don't understand why would you use such approach, you can compare these 2 objects to check if the user is logged in:

if ($userLoggedOutAt < $userLoggedInAt) {
    echo 'The user is logged in';
}

Question: Using your approach, how would you set the logout time if the user is closing the browser?