1

I don't know what kind of value I should store in the database.

I'm thinking I should do something like this, but I don't know how:

Store the years, days, hours, minutes since a give date like 1/1/2000 Then at another time I will compare that value against the current years, days, hours, and minutes since 1/1/2000. Obviously, the difference between the two would be the time since the user's last login.

I appreciate any help anyone can provide on this. Thanks in advance

S.L. Barth is on codidact.com
  • 8,198
  • 71
  • 51
  • 66
Milkbones
  • 41
  • 1
  • 3
  • Yeah, a datetime field is okay. You update it each time user logs in, and then get the difference between the current date and the one stored – Nicolás Torres Jun 21 '12 at 22:31
  • You should store the unix timestamp. That is the number of seconds since Jan 1, 1970 and is extremely well supported across just about every language. – kingcoyote Jun 21 '12 at 22:32

3 Answers3

3

Why not just use a TIMESTAMP or a DATETIME? That's literally all you need.

Niet the Dark Absol
  • 320,036
  • 81
  • 464
  • 592
3

mysql will do this for you. It will store the number of seconds since 1 Jan 1970. And calculate the difference.

Try this: SELECT NOW();. You will see now's date/time. Try SELECT UNIX_TIMESTAMP(NOW()); and you'll see the number of seconds since Epoch (but you don't need that usually).

You should store this value everytime the user logs in, which means insert now() into a table or update a last_login field.

You have two options:

INSERT INTO user_login_history (user_id, login_date) VALUES (1, NOW());

and

UPDATE users SET last_login=NOW() WHERE user_id=1;

Note that the last_login and login_date fields will have to be of either DATETIME type (to store date and time) or TIMESTAMP or DATE (only date) or TIME (only time).

Also, you can do time calculations in mysql with DATEDIFF, like so:

SELECT NOW(), DATEDIFF(NOW(), DATE('2012-6-5'));

In your table, SELECT DATEDIFF(NOW(), last_login) AS "days_since_last_login" FROM users;

jadkik94
  • 7,000
  • 2
  • 30
  • 39
  • If I use: SELECT NOW(), DATEDIFF(NOW(), DATE('2012-6-5')); How do I get the value into a variable so that I can use echo to print it? – Milkbones Jun 21 '12 at 23:52
  • You need to use `mysql_query`. Check [the example](http://www.php.net/manual/en/function.mysql-query.php) to know how to use it. If it's not cleat enough, I'll edit my answer. – jadkik94 Jun 22 '12 at 00:23
1

You can store the timestamp of the last login and then just do the arithmetic. Maybe you should look at the DateTime::diff method to let PHP do the hard maths ;-)

Konrad Neuwirth
  • 898
  • 5
  • 8