GROUP BY same record but different TIMESTAMP/DATETIME

89
February 21, 2018, at 12:29 PM

I'm trying to GROUP BY same records but different timestamp or datetime. The difference of time is only about 3 minutes from the first entry.

example:

This is what the database table looks like.

*-------------------------------------------*
|   id  |   name    |         time          |
|   1   |   Lei     | 2018-02-21 12:00:10   |
|   2   |   Lei     | 2018-02-21 12:01:11   |
|   3   |   Lei     | 2018-02-21 12:01:15   |
|   4   |   Lei     | 2018-02-21 12:01:16   |
|   5   |   Anna    | 2018-02-21 12:03:11   |
|   6   |   Anna    | 2018-02-21 12:03:13   |
|   7   |   Bell    | 2018-02-21 12:05:01   |
|   8   |   Lei     | 2018-02-21 12:10:00   |
*-------------------------------------------*

I want to get Lei's entry from 12:00:10 up to 3 minutes from her first timestamp or datetime record.

so the output would be like this.

*-------------------------------------------*
|   id  |   name    |         time          |
|   1   |   Lei     | 2018-02-21 12:00:10   |
|   5   |   Anna    | 2018-02-21 12:03:11   |
|   7   |   Bell    | 2018-02-21 12:05:01   |
|   8   |   Lei     | 2018-02-21 12:10:00   |
*-------------------------------------------*

I'll be gladly appreciate your help, mysql or php it is.

Answer 1

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE Table1
    (`id` int, `name` varchar(4), `time` datetime)
;
INSERT INTO Table1
    (`id`, `name`, `time`)
VALUES
    (1, 'Lei', '2018-02-21 12:00:10'),
    (2, 'Lei', '2018-02-21 12:01:11'),
    (3, 'Lei', '2018-02-21 12:01:15'),
    (4, 'Lei', '2018-02-21 12:01:16'),
    (5, 'Anna', '2018-02-21 12:03:11'),
    (6, 'Anna', '2018-02-21 12:03:13'),
    (7, 'Bell', '2018-02-21 12:05:01')
;

Query 1:

select id, name, min(time) as time
from Table1 
group by name
order by time

Results:

    | id | name |                 time |
    |----|------|----------------------|
    |  1 |  Lei | 2018-02-21T12:00:10Z |
    |  5 | Anna | 2018-02-21T12:03:11Z |
    |  7 | Bell | 2018-02-21T12:05:01Z |

OR if you want to group by interval 3 minute you can do it like this

select id, name, min(time) as time
from Table1 
group by name, UNIX_TIMESTAMP(time) DIV 180
order by time
;
Answer 2

With your sample data, you don't need to consider the timestamp at all:

select (@rn := @rn + 1) as id, name, min(time) as time
from t cross join
     (select @rn := 0) params
group by id, name;

Grouping things by three minute intervals, from the first record in the interval is much harder. This requires either variables or recursive CTEs.

Answer 3

Looks like you need something like this:

select *
from mytable t
where not exists (
  select *
  from mytable t1
  where t1.name =  t.name
    and t1.id   <> t.id
    and t1.time >= t.time - interval 3 minute
    and t1.time <  t.time
);

Demo: http://sqlfiddle.com/#!9/03cf16/1

It will select rows only if no row with the same name exists within a three munutes interval.

READ ALSO
How to do SUM(MAX( ) + MAX( ))?

How to do SUM(MAX( ) + MAX( ))?

Is there a proper way to do SUM(MAX() + MAX())?

158
How can I increment from 1st to 2nd to 3rd when an existing Data is present in VB.net/Mysql

How can I increment from 1st to 2nd to 3rd when an existing Data is present in VB.net/Mysql

Seriously need helpBeen figuring this one out for days and I can't seem to get it

111
Issues with writing to dynamically named MySQL table

Issues with writing to dynamically named MySQL table

I am trying to create dynamically named MySQL tables in routines using a prefix based on a model run number which I set in php as the @ModelRef variable

105