CODEWITHSUNDEEP
mysql
Fero
Fero

Reputation: 13315

How to calculate hours in mysql

My problem is in brief here...

Once a user signed in i stored his login date in the users table. If the user doesn't logged in for 72 hours i need to change his status to inactive.

How can i able to find whether 72 hours is completed or not after the user logged in using My Sql.

Thanks in advance...

Upvotes: 0

Views: 3111

Answers (3)

tpow
tpow

Reputation: 7884

Create a CRON routine to run every hour with this query:

  UPDATE users
  SET status = 'Inactive' 
  WHERE (SELECT * FROM users WHERE last_login < now() - 259200)

To answer your question more specifically, it is the where clause, when ran, that tells you all the users that haven't logged in for 72 hours.

SELECT * FROM users WHERE last_login < now() - 259200

However, there is no way to set each user to inactive at exactly 72 hours. To get more accurate than the solution provided above, run the query more often.

*Note - insert your columns names where appropriate. Query not tested. 259200 = # of seconds in 72 hours - assumes you store your timestamps seconds (Epoch)

Upvotes: 3

Kyle Humfeld
Kyle Humfeld

Reputation: 1907

I'd recommend using the TIMEDIFF() function, which you can find documented here: dev.mysql.com timediff doc.

In your case, I'd format my where clause something like this:

WHERE
TIMEDIFF(CURTIME(), LastLoginDate) > '3 0:0:0.0'

or

WHERE
TIMEDIFF(CURTIME(), LastLoginDate) > '72:0:0.0'

I haven't done this specifically, but the base concept should work for you.

Upvotes: 5

Oswald
Oswald

Reputation: 31647

Use DATETIME type to store dates, subtract 72 hours from NOW() using DATE_SUB() and see if the result is larger than the value stored in the database.

Upvotes: 0

Related Questions