Mysql order records by similar time and same user

Question

I have these records in MySql table

   id  |  record_date            |   record_user
   1   |  4/20/2015 5:00:00 PM   |   Kenny
   2   |  4/20/2015 5:08:00 PM   |   Kyle
   3   |  4/20/2015 5:08:20 PM   |   Stan
   4   |  4/20/2015 5:09:00 PM   |   Kyle
   5   |  4/20/2015 6:40:00 PM   |   Cartman
   6   |  4/20/2015 6:41:00 PM   |   Timmy
   7   |  4/20/2015 6:45:00 PM   |   Timmy
   8   |  4/20/2015 6:45:20 PM   |   Timmy
   9   |  4/20/2015 6:45:30 PM   |   Stan

And i want to order those records by record_user and a 2 minutes time window. For example: Kyle have two records between in 5:08 and 5:09 and must be stick together.

 record_date           |  ids |  record_user
4/20/2015 5:08:00 PM   |  2,4 | kyle

in other case, Stan have 2 records but are in different time window

 record_date           |  ids |  record_user
4/20/2015 5:08:20 PM   |    3 | stan
4/20/2015 6:40:00 PM   |    9 | stan

For all the table in this example, the result must be

 record_date           |  ids |  record_user
4/20/2015 5:00:00 PM   |    1 | kenny
4/20/2015 5:08:00 PM   |  2,4 | kyle
4/20/2015 5:08:20 PM   |    3 | stan
4/20/2015 6:40:00 PM   |    5 | cartman
4/20/2015 6:40:00 PM   |6,7,8 | timmy
4/20/2015 6:40:00 PM   |    9 | stan

I can concatenate the id's in ids and get the record_user, and record_date but i don't know how do the date thing. In this question @pala_ (thanks man, u rock). He explain how deal whit time in mysql very well and help me a lot to understand the time window trouble but mysql is a headache for me right now.

Answer 1

With the caveat that I think your expected result is wrong (ie, showing wrong dates for certain groups, showing grouped ids that shouldn't be grouped together such as showing three values for timmy where only two fall within a two minute window), and also with the caveat that you want to show the first time from the group rather than the last, the following query will do it:

  select min(record_date) record_date, group_concat(id order by id asc) ids, record_user
    from (
      select if(((record_date < @last_record + interval 2 minute) and (@last_user = record_user)) or @last_record = '0000-00-00 00:00:00', 
               if((@last_record := record_date) and (@last_user := record_user), @group, @group),
               if((@last_record := record_date) and (@last_user := record_user), @group := @group + 1, @group := @group + 1)) subgroup, records.* 
        from records, (select @last_record := '0000-00-00 00:00:00', @last_user := '', @group := 1) q
        order by record_user, record_date asc
  ) q 
  group by subgroup
  order by min(record_date) asc;

As mysql does not have any windowing functionality, it's necessary to create a per group ranking with variables, and in order to force mysql to evaluate our variables in a certain order, we have to wrap them in a bunch of conditional statements. This makes the query ugly, but it functions.

results here