SQL - Compare DateTime to current time

Question

So, I have isOnline (boolean) and lastTimeSeen (DateTime) fields in my users table.

Everytime a user loads a page on my application, lastTimeSeen will be updated for him.

Then, I want to make a SQL query that will retrieve all users that have the isOnline column equals to 1 and where lastTimeSeen column is superior to 5 minutes to set isOnline to 0.

Though, I have no idea how to compare a DateTime with current time in order to check if the user has not been seen in the last 5 minutes.

Do I have to do that with PHP?

UPDATE FROM users WHERE isOnline = 1 AND lastTimeSeen => comparison to make

Could you guys help me with that?

Maybe the answers here will help: https://stackoverflow.com/questions/5182275/datetime-equal-or-greater-than-today-in-mysql — Michael Heidelberg, Jan 02 '19 at 16:49
Possible duplicate of [Compare dates in MySQL](https://stackoverflow.com/questions/3651985/compare-dates-in-mysql) — simon.ro, Jan 02 '19 at 16:52

score 3 · Accepted Answer · answered Jan 02 '19 at 16:50

3

UPDATE users 
SET isOnline = 0
WHERE isOnline = 1 
AND lastTimeSeen < now() - interval 5 minute

answered Jan 02 '19 at 16:50

juergen d

201,996
37
293
362

1

I think it should be `>=`, right? Otherwise it will be > 5 minutes ago. – user3783243 Jan 02 '19 at 16:51
Thanks, it seems to work. Though this syntax "now() - interval 5 minute" looks really weird to me. Edit : just noticed this is a substraction – Snyte Jan 02 '19 at 16:56
@user3783243: It should be ***superior** to 5 minutes*. So `<` is correct. – juergen d Jan 02 '19 at 17:13

score 1 · Answer 2 · answered Jan 02 '19 at 16:55

1

You can use:

WHERE MY_DATE_FIELD <= NOW() - INTERVAL 5 MINUTE

answered Jan 02 '19 at 16:55

Tarreq

1,282
9
17

SQL - Compare DateTime to current time

2 Answers2