Find the largest time-free data list in the database

Question

I have a database containing events. Each event has a timestamp. Events are evenly distributed every other day, but every night there is a period of time without data. My problem is that the night is not defined. It can be from 23:00 to 7:00 the next day, or from 2:00 to 10:00 on the same day, or even from 8 pm to 23 pm on the same day.

Now I want to calculate the interval of events, but without long periods of time without events. But I do not know how to find this period of time. My problem is that there can be days without a difference or two days with the same time interval (for example, from 8 pm to midnight on the first day, from midnight to 7 am on the second day).

My question is: how to find this period of time?

I would prefer a solution only in MySql, but if that is not possible, you could use PHP too.

Answer 1

How about self-joining a table with the next row and then doing the time difference between the joined tables and finding the maximum diff?

Assuming your schema looks like this ( and the supposed entries are in a temporary order ):

CREATE TABLE log ( id INT NOT NULL AUTO_INCREMENT, occurred_at DATETIME, event VARCHAR(255), PRIMARY KEY (id), INDEX (occurred_at) ); 

Something like that:

 SELECT TIMEDIFF(after.occurred_at, before.occurred_at) AS time_gap, `before`.*, `after`.* FROM log `before` JOIN log `after` ON after.id = before.id+1 ORDER BY time_gap DESC LIMIT 1; 

Answer 2

Why don't you just process the list of events sorted and look for the largest gap in each day?

Finding spaces in a sorted list is trivial. Define some threshold values, for example. "min. 1 hour" and "overlaps with an interval of 12-6", and then you have full detection of spaces.

This is not really “data mining,” by the way. - This is just a simple data request.