Solved

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

Posted on 2014-08-13
3
114 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 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 110

Expert Comment

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

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

738 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