Query To Get Consecutive Events If They Are Close Enough

Question

This is my table structure:
monResults:

id Int(10)
monDateTime Datetime()
typeId int(10)

I need a query to get all monResults that have consecutive monDateTimes that are less than 1 minute apart and have the same typeId. I want to be able to get X number of these events. Mostly I don't know how to add the condition to make consecutive results be less than 1 minute apart.

Answer 1

Have you tried to do this at all? Do you want to pass in a typeId, and have the query return all the ids that are less than one minute apart?

The problem is that you will just get a sorted list, you would still need to parse it in a program, as you will get, in a very contrived example, where monDateTime is a variant of a unit timestamp, for ease of typing.

id=1, monDateTime=3
id=5, monDateTime=57
id=2, monDateTime=63
id=3, monDateTime=120

So, you would need to determine in your application that the first two are one group, but the second and third are a group, so id=2 and 5 are in two groups.

If you loop through in a program and state that I want all the ids that are within a minute of a specific id and typeId then you can organize it in the program.

Then you can just use the TimeDiff function to subtract your specific id and the ids in the database: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_timediff

Answer 2

There are some answers in this question which might lead you in the right direction:

http://stackoverflow.com/questions/1875307/group-events-by-temporal-distance-in-sql/1875570#1875570

Answer 3

SELECT  *
FROM    (
        SELECT  (@typeID = typeID AND @pdt >= monDateTime - INTERVAL 1 MINUTE) AS con,
                @typeID := typeID,
                @pdt := monDateTime 
        FROM    (
                SELECT  @typeID := NULL,
                        @pdt := NULL
                ) vars,
                mytable
        ORDER BY
                monDateTime 
        ) q
WHERE   con