Databases
StackOverflow2013
Postsdbo
POmySQL select rows from a single table for each user_id which are close in timestamp
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmySQL select rows from a single table for each user_id which are close in timestamp
    text
    copied!<p>Not sure how best to word this, so please bear with me. My table (simplified) is as follows:</p> <ul> <li><code>id</code> - Integer - auto increment</li> <li><code>user_id</code> - Integer</li> <li><code>timestamp</code> - Datetime</li> </ul> <p>What I need is the ability to query this table and select all records where the <code>timestamp</code> columns are within a predefined time range (potentially arbitrary, but lets say 10 minutes) for each <code>user_id</code>.So, for example, I would like to know if there is an entry for hypothetical <code>user_id</code> 5 at "2011-01-29 03:00:00" and then next at "2011-01-29 03:02:00" but not if a user searched once at "2011-01-29 03:00:00" and then next at "2011-01-29 05:00:00". This would also need to capture instances where a user searches more than 2 times, each within the time range of the previous.</p> <p>For background, this is a table of site searches, and I would like to know all instances where a user searches for something, then searches again (presumably because their previous search did not provide the results they were looking for). </p> <p>I know this is probably simpler than I am making it out to be, but I can't seem to figure it out. I can clarify or provide additional info if needed. Thanks!</p> <p><strong>EDIT:</strong> I am interested in the search returning results for all of the users in the table, not just user #5, and also to search without input of the actual times. The timestamp should not be something which is manually input, but should instead should find rows by each user which are within 10 minutes of one another.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload