MySQL count rows within the same intervals to eachother -

MySQL count rows within the same intervals to eachother -

i have table 1 column date:

+----------+---------------------+ | id | date | +----------+---------------------+ | 5 | 2012-12-10 10:12:37 | +----------+---------------------+ | 4 | 2012-12-10 09:09:55 | +----------+---------------------+ | 3 | 2012-12-09 21:12:35 | +----------+---------------------+ | 2 | 2012-12-09 20:15:07 | +----------+---------------------+ | 1 | 2012-12-09 20:01:42 | +----------+---------------------+

what need, count rows illustration whitin 3 hours each other. in illustration want bring together upper row 2nd row, , 3rd row 4th , 5th rows. output should this:

+----------+---------------------+---------+ | id | date | count | +----------+---------------------+---------+ | 5 | 2012-12-10 10:12:37 | 2 | +----------+---------------------+---------+ | 3 | 2012-12-09 21:12:35 | 3 | +----------+---------------------+---------+

how this?

i think need self-join this:

select t.id, t.date, count(t2.id) t left outer bring together t t2 on t.date between t2.date - interval 3 hr , t2.date + interval 3 hr grouping t.id, t.date

(this untested code might have syntax error.)

if trying split 3-hour intervals, can like:

select max(t.date), t.id, count(*) (select t.*, (date(date)*100 + floor(hour(date)/3)*3) interval t ) t grouping interval

mysql count intervals