mySQL select rows from a single table for each user_id which are close in timestamp

Not sure how best to word this, so please bear with me. My table (simplified) is as follows:

• id - Integer - auto increment
• user_id - Integer
• timestamp - Datetime

What I need is the ability to query this table and select all records where the timestamp columns are within a predefined time range (potentially arbitrary, but lets say 10 minutes) for each user_id.So, for example, I would like to know if there is an entry for hypothetical user_id 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.

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).

I know this is probably simpler than I am making it out to be, but I can't seem to figure it ou开发者_运维技巧t. I can clarify or provide additional info if needed. Thanks!

EDIT: 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.

SELECT distinct t1.user_id, t1.another_field, t1.another_field
FROM
    table t1,
    table t2
WHERE
t1.user_id = t2.user_id
AND abs(timestampdiff(MINUTE, t1.timestamp, t2.timestamp)) < 10

if you want to further limit the results, you can add

AND t1.user_id = any_number (or IN or between, etc)

To restrict date range add,

AND t1.timestamp BETWEEN A and B (or > or <)

This should give you all users and theirs number of searches within time limit:

SELECT user_id, COUNT(*) AS cnt FROM table 
   WHERE timestamp BETWEEN "2011-01-29 03:00:00" AND "2011-01-29 03:02:00" 
   GROUP BY user_id
   ORDER BY user_id

This will show you number of searches made just by user_id #5:

SELECT COUNT(*) AS cnt FROM table 
   WHERE user_id=5 
   AND timestamp BETWEEN "2011-01-29 03:00:00" AND "2011-01-29 03:02:00"

Depending on actual DB the syntax might be somewhat different, especially the format of dates passed to BETWEEN condition.