Vfrdtyky

Well, I have a table with some columns that some of these are repeated, but the time is not exactly the same, I need to do a group by that has a range of 2 hours.

Notice that all the fields marked in black repeat, but the datetime field may differ by a few minutes, so I need to make a group by joining everyone within that two-hour interval.

It would be something like: SELECT * FROM tabela GROUP BY sinistro, prefixo, data AND horaIfull_oco range 2h

And also in the result it is necessary that the columns come with all their ids if possible, eg: id1, id2 etc etc

He would join all those who had a maximum of two hours apart.
I have already researched and found nothing, if anyone knows how to do.

You can select floor(hour / 2) which will tell you which two hour period it belongs in and group by that along with the date:

SELECT *,

    group_concat(id_oco) as grouped_ids,

    floor(HOUR(horaIfull_oco) / 2) AS two_hour

FROM tabela

GROUP BY inclusao_oco, sinistro, prefixo, data, DATE(horaIfull_oco), two_hour

GROUP BY FLOOR(UNIX_TIMESTAMP(horaIfull_oco) / 7200)

(7200 is 2 hours.)

If you want to get the base time for each group:

FROM_UNIXTIME(

   FLOOR(UNIX_TIMESTAMP(horaIfull_oco) / 7200)

              , 7200)

This technique can be adapted to any regular time interval by changing 7200.

Assuming a database table having a table with the following schema:

CREATE TABLE t1 (

    c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    c2 VARCHAR(100),

    c3 VARCHAR(100),

    c4 DATE,

    c5 TIME,

    c6 DATETIME);



INSERT INTO t1 (c1,c2,c3,c4,c5,c6) 

VALUES 

(1,'DDA1123_12112018','55307','2018-12-11','00:43:00', '2018-12-11 00:43:00'),

(2,'DDA1123_12112018','55307','2018-12-11','00:07:00', '2018-12-11 00:07:00'),

(3,'DDA1124_12112018','55308','2018-12-11','03:07:00', '2018-12-11 03:07:00'),

(4,'DDA1124_12112018','55308','2018-12-11','04:07:00', '2018-12-11 04:07:00'),

(5,'DDA1124_12112018','55308','2018-12-11','07:07:00', '2018-12-11 07:07:00'),

(6,'DDA1125_12112018','55309','2018-12-11','06:07:00', '2018-12-11 06:07:00'),

(7,'DDA1125_12112018','55309','2018-12-11','07:57:00', '2018-12-11 07:57:00'),

(8,'DDA1126_12112018','55310','2018-12-11','08:07:00', '2018-12-11 08:07:00'),

(9,'DDA1127_12112018','55311','2018-12-11','12:07:00', '2018-12-11 12:07:00'),

(10,'DDA1127_12112018','55311','2018-12-11','14:07:00', '2018-12-11 14:07:00'),

(11,'DDA1127_12112018','55311','2018-12-11','17:07:00', '2018-12-11 17:07:00');

In that case you could use CASE ... WHEN in GROUP BY like in this example query.

SELECT c1,c2,c3,c4,c5,c6,

CASE TRUE

WHEN HOUR(c6) BETWEEN 0 AND 2 THEN 0 

WHEN HOUR(c6) BETWEEN 2 AND 4 THEN 2 

WHEN HOUR(c6) BETWEEN 4 AND 6 THEN 4 

WHEN HOUR(c6) BETWEEN 6 AND 8 THEN 6

WHEN HOUR(c6) BETWEEN 8 AND 10 THEN 8

WHEN HOUR(c6) BETWEEN 10 AND 12 THEN 10

WHEN HOUR(c6) BETWEEN 12 AND 14 THEN 12

WHEN HOUR(c6) BETWEEN 14 AND 16 THEN 14

WHEN HOUR(c6) BETWEEN 16 AND 18 THEN 16

WHEN HOUR(c6) BETWEEN 18 AND 20 THEN 18

WHEN HOUR(c6) BETWEEN 20 AND 22 THEN 20

WHEN HOUR(c6) BETWEEN 22 AND 24 THEN 22

END AS c7 FROM t1

GROUP BY c7;

You can view the results of the above example in SQL Fiddle.

EDIT

For better results the above query could be re-written:

SELECT c1,c2,c3,c4,c5,c6,

CASE TRUE

WHEN HOUR(c6) BETWEEN 0 AND 1 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 0 

WHEN HOUR(c6) BETWEEN 2 AND 3 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 2 

WHEN HOUR(c6) BETWEEN 4 AND 5 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 4 

WHEN HOUR(c6) BETWEEN 6 AND 7 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 6

WHEN HOUR(c6) BETWEEN 8 AND 9 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 8

WHEN HOUR(c6) BETWEEN 10 AND 11 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 10

WHEN HOUR(c6) BETWEEN 12 AND 13 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 12

WHEN HOUR(c6) BETWEEN 14 AND 15 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 14

WHEN HOUR(c6) BETWEEN 16 AND 17 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 16

WHEN HOUR(c6) BETWEEN 18 AND 19 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 18

WHEN HOUR(c6) BETWEEN 20 AND 21 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 20

WHEN HOUR(c6) BETWEEN 22 AND 23 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 22

END AS c7 FROM t1

GROUP BY c7;

You can view the results of the edited example in this SQL Fiddle.