CODEWITHSUNDEEP
mysqlsqlgps
G unitzo
G unitzo

Reputation: 89

Group rows by time interval

i have a database with very much rows from a gps sender. The gps have 1 seconds delay to send next row to the database. So what i want to do is a web interface that shows travels, i dont want to show much rows, i want to group the rows to trips. So i want to do is a query who can declare a trip/travel by checking if its more then 14 minutes to next row, if it is then make a row of all rows before a give it a trip number, else add it to the "travel" collection.

Upvotes: 1

Views: 183

Answers (1)

AgRizzo
AgRizzo

Reputation: 5271

Try this (example is at http://sqlfiddle.com/#!2/a0c86/39)

SELECT Trip, MIN(Date_Time), MAX(Date_Time)
FROM (
  SELECT  @Trip := IF(TIMESTAMPDIFF(MINUTE, @Date_Time, Date_Time) <= 20, @Trip, @Trip+1) AS TRIP
    , logid
    , @Date_Time := Date_time AS Date_Time
  FROM gpslog
  JOIN (SELECT @TRIP := 1, @Date_Time := null ) AS tmp
  ORDER BY Date_Time) AS triplist
GROUP BY Trip

Upvotes: 2

Related Questions