What kind of query is it possible to group MYSQL data with the time difference between the nearest ones no more than an hour?

That is, if the time difference of each subsequent recording is not more than 60 minutes from the previous one, then it falls into one group.

  • A heap of records in half an hour each according to the described technology should also fall into one group, it turns out ... the second is in one group with the first, the third is in one group with the second, which is in the same group with the first ... and so on. - Akina
  • Well, yes, that’s right - Ivan_Aka
  • Then the solution is seen in the preliminary search for the boundaries of the ranges - searching for pairs of neighbors when sorting by time records, between which more than an hour. And using this query as an additional data source with ranges in the main query for grouping by entry into the range. - Akina

2 answers 2

select date, @grp:=@grp+if(date>@prev+interval 30 minute,1,0) as GRP, @prev:=date from TimeTest, (select @prev:=NULL, @grp:=1) X order by date

Gives the numbers of groups according to the time difference. To group by this field, you need to wrap it in one more select and group it by the GRP field:

 select ... from ( запрос-показанный-выше ) A group by GRP

Test on sqlfiddle.com

  • Yes, it turns out beautiful, thanks! - Ivan_Aka

SELECT FLOOR ( time / (60 * 60)) * (60 * 60) as t FROM table GROUP BY t

Maybe so?

  • If this is not the answer that solves your question, it is better to complete the question itself by clicking 'Edit' under it. If you have other answers, if the problem has not yet been resolved, you can not wait (no one knows whether the issue is resolved or not). In general, something tells me that it will be necessary to use variables to access the data from the previous record, if of course I correctly understood the question ... - Mike
  • It looks like you're right, but how to apply such variables? - Ivan_Aka