Storing user login-logout time into database with it's difference

Question

I want to store user's login history on my site.

table:

mysql> desc user_stat;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | varchar(64) | YES  |     | NULL    |       |
| login  | varchar(64) | YES  |     | NULL    |       |
| logout | varchar(64) | YES  |     | NULL    |       |
| diff   | varchar(64) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

When user log In I perform insert.

insert into user_stat(id,login) values('".$id."','".NOW()."');

But for logout situation is confusing. As user may have logged in twise in two diff browser. So how can I maintain logout time? something like session management (though I have not used session var for login logout). JS sdk for login logout I used : http://jsfiddle.net/YCFpH/

Also How can I get the differene of logout and login time in diff column?

One way is to convert string of data-time into number and getting differece of it. But I dont what that method.

Anyother solution for data time diff?

This may be of some help: http://www.php.net/manual/en/datetime.diff.php — Funk Forty Niner, Nov 13 '13 at 20:48
You could also make use of [`BETWEEN`](http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#operator_between) taken/found from http://stackoverflow.com/a/10792045/1415724 and http://stackoverflow.com/a/12386749/1415724 — Funk Forty Niner, Nov 13 '13 at 20:56
At minimum, your login/logout columns should be timestamp/datetime; you don't want to have to do conversions every time you query that table. And please avoid the use of `BETWEEN` [with timestamps](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common) - MySQL only gets a some-what pass due to the fact that seconds are default (but precision can be higher!). Perhaps a better way to think about missing logouts is: if you record state changes, you only need the start of the next state - ergo, logout time is also the new login time. — Clockwork-Muse, Dec 26 '13 at 00:06

score 1 · Answer 1 · edited May 23 '17 at 10:31

But for logout situation is confusing. As user may have logged in twise in two diff browser. So how can I maintain logout time?

Each Login must have some sort of session identifier. Otherwise, you will never properly correlate your logout time with the correct login time. There are any number of ways to get this and that will depend on your web server platform. At the very least, you can create a uid on the client (see Create GUID / UUID in JavaScript? ) and pass the uid in when the user logs out.

Additionally, if you are not sorting by or joining by the difference time, login and logout should likely be datetime datatypes and the difference simply calculated each time you want it instead of stored. For example.

    set @time1 = now();
    set @time2 = date_add(now(), interval 30 second);

    select @time1, @time2, (timediff(@time1,@time2))

Now you get the difference between the times in seconds.

2013-12-26 14:17:32 2013-12-26 14:18:02 -00:00:30

Storing user login-logout time into database with it's difference

1 Answers1