How to choose a date range

Question

This is my table:

+------+--------+-------------------+
| user | item   | date_time         |
|   10 |     01 | 10-10-10 20:10:05 |
|   10 |     02 | 10-10-10 20:10:10 |
|   10 |     03 | 10-10-10 20:10:10 |
|   20 |     02 | 10-10-10 20:15:10 |
|   20 |     02 | 10-10-10 20:20:10 |
|   30 |     10 | 10-10-10 20:01:10 |
|   30 |     20 | 10-10-10 20:01:20 |
|   30 |     30 | 10-10-10 20:05:20 |
+------+--------+-------------------+

I want to make a query that returns a user who took several elements in 1min, for example, this result:

+------+--------+-------------------+
| user | item   | date_time         |
|   10 |     01 | 10-10-10 20:10:05 |
|   10 |     02 | 10-10-10 20:10:10 |
|   10 |     03 | 10-10-10 20:10:10 |
|   30 |     10 | 10-10-10 20:01:10 |
|   30 |     20 | 10-10-10 20:01:20 |
+------+--------+-------------------+

How can i do this?

---

Edit

And if I want to show only users who appear on this output 2 or more times?

Example:

+------+--------+-------------------+
| user | item   | date_time         |
|   10 |     01 | 10-10-10 20:10:05 |
|   10 |     02 | 10-10-10 20:10:10 |
|   10 |     03 | 10-10-10 20:10:10 |
+------+--------+-------------------+

Answer 1

Yes, you need to add a primary key (id) as suggested by @Will.

To retrieve each item once (and only once) no matter how many matches there were in a 1-minute window, try a subquery instead of a full join:

Select user,item,date_time from my_table t1
  where id in (select t2.id from my_table t2,my_table t3
      where t2.id <> t3.id and t2.user = t3.user 
           and abs(t2.date_time - t3.date_time) < 60)

- edit-- , . ", 3 60 " ", 2 ". : , ( ) "temp1":

select * from my_table where user in 
    (select user from temp1 group by user having count(*) > 2);

Answer 2

( T1 T2). WHERE, , T1.user T2.user, T1.date_time T2.date_time .

, , , , . ( ) WHERE, T1.id <> T2.id.

, () :

SELECT *
FROM stuff T1, stuff T2
WHERE T1.user = T2.user
  AND ABS(UNIX_TIMESTAMP(T1.date_time) - UNIX_TIMESTAMP(T2.date_time)) < 60
  AND T1.id <> T2.id;

Answer 3

try the following:

SELECT * FROM myTable  
JOIN (SELECT MAX(date_time) AS maxi, MIN(date_time) AS mini, user AS uid FROM myTable GROUP BY uid) AS otherTable  
ON date_time<=maxi AND date_time>=mini AND user = uid AND UNIX_TIMESTAMP(maxi) - UNIX_TIMESTAMP(mini) < 60  
GROUP BY uid, date_time