How to count unique records within a time range in MySQL?

Question

I have a table with the below definition:

CREATE TABLE visit_log ( id INT NOT NULL , door_entry TIMESTAMP NOT NULL);  

This table has the below records:

insert into visit_log values(1, '2009-04-15 15:00.000');
insert into visit_log values(1, '2009-04-15 15:20.000');
insert into visit_log values(1, '2009-04-15 15:55.000');
insert into visit_log values(2, '2009-04-15 15:00.000');
insert into visit_log values(2, '2009-04-15 15:40.000');
insert into visit_log values(2, '2009-04-15 16:20.000');
insert into visit_log values(3, '2009-04-15 15:00.000');  

Now I want to find that how many times a person has visit_log within each 30 minutes, I mean all the visits at the range of 30 minutes should be counted as 1 visit, my desired result is shown below:

id         visits  
 1            2         
 2            3  
 3            1 

id 1 gets two visits because the first two visits are counted as one visit as they happened within the time span of 30 minutes, id 2 has three visits because all of three visits came in the time difference of more than 30 minutes and the id 3 has one visit.

Any help is appreciated in advance.

Answer 1

This query will give you the answer you are looking for:

select s.id, count(*) as visits from 
(select distinct id, concat(hour(door_entry), floor(minute(door_entry)/30)) 
from visit_log) s group by s.id