?
Solved

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

Posted on 2014-08-13
3
Medium Priority
?
116 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 6

Accepted Solution

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

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

Open in new window

0
 
LVL 111

Expert Comment

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

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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

770 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