Solved

Need Help with a SQL query to select records with last action < 5 mins ago

Posted on 2014-08-13
3
113 Views
Last Modified: 2015-02-06
I have a users table with a field "last_action" (DATETIME field) which records the time of the last activity of a user on the site. This allows us to show the users as "online" or not.

Please tell me the mysql query needed to pull all users who's "last_action" field is 5 minutes ago or less. .... something like:

select * from users where last_action <= 10minutesago

Thanks!
0
Comment
Question by:tonyhhisc
  • 2
3 Comments
 
LVL 6

Accepted Solution

by:
rjohnsonjr earned 500 total points
ID: 40259799
Try this:

select * from users where last_action <= DATE_SUB(NOW(), INTERVAL 10 MINUTE)

Open in new window

0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 40259840
Five minutes or ten minutes?  With computers, accuracy matters!
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 40594948
Yep, it's either five or ten and after waiting for months to hear back from you, we still don't know what you really want!  

But that aside, you cannot really know whether a user is "online" or not.  What if I visit your site and then turn off my computer, or leave and get some coffee?  Am I "online?"  The nature of the HTTP request/response system can only tell you if your server has gotten a request, not whether a client is paying attention to the response.  You can use AngularJS or jQuery to repeat the requests over and over, thus getting a string of client requests, and you can make some assumptions about whether the user is "online" based on the timing of the string of repeated requests.  But even these assumptions are uncertain -- I can open a browser window and visit your site, thus starting the jQuery timers that would repeatedly query your server.  Then the question becomes again, Am I "online?"  Or have I just left the browser tab open while I went out coffee?

It seems that your strategy will tell you if the client visited the site in some recent period, but that's about all you can know - the whole concept of an "online user" is seductive, but does not exist in the RESTful world of the WWW.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question