Solved

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

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

Expert Comment

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

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now