How do we get list based on time difference of NOW() and last time

Databases
1

Hi all,

We have a MySQL Table called users_chatting
with one of the fields being: last_activity which is of time datetime

How do we then issue a SELECT command to get a list of all those entries whose last_activity was
say more than 300 Seconds ago?

I tried this but does not work:

SELECT id FROM users_chatting WHERE (TIMESTAMPDIFF(SECOND, NOW(), loged_in) > 300) ORDER BY id DESC

Thanks,

2

try it without the parentheses around the WHERE clause

also, use last_activity instead of loged_in

3

Hi,

I tried it but is producing NO results!

So I am using this SELECT:

SELECT id FROM users_chatting WHERE TIMESTAMPDIFF(SECOND, NOW(), last_activity) > 3 ORDER BY id DESC;

Value of last_activity is: 2018-04-08 11:57:41
Value of Now is: 2018-04-08 12:02:03
Of course NOW is way passed 3 Seconds after last_activity

Thanks

4

your first stop in any debugging effort should be to look up the syntax in da Manual

[quote]
TIMESTAMPDIFF( unit, datetime_expr1, datetime_expr2)

Returns datetime_expr2 − datetime_expr1[/quote]

i had assumed, without checking, that you had coded the variables in the right order :slight_smile:

5

Hey,

Got it working OK. The problem/issue is that that SELECT command actually produces a negative value.
So as long as the WHERE is checking for < negative value, then it produces the desired results
Cool :slight_smile:

Thanks,
Dean

6

or you could just list the function variables in the other order!

TIMESTAMPDIFF(SECOND, last_activity, CURRENT_TIMESTAMP) > 300
7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.