Mysql – How to compare all rows in a table and extract data using triggers

MySQL

I have two tables, one table stores the GPS data of various users and another table stores the idle time of the users based upon the GPS latitude and longitude values and timestamp difference.

Table T1 contents:

id  uid  latitude   longitude    received_time          imno
435 25  13.041868   80.198868   2014-08-07 15:53:00     352944054874253 
436 25  13.041862   80.198868   2014-08-07 15:54:00     352944054874253 
437 25  13.041869   80.198868   2014-08-07 15:55:00     352944054874253 
438 25  13.041887   80.198871   2014-08-07 15:55:38     352944054874253 
439 25  13.041887   80.198871   2014-08-07 15:55:42     352944054874253 
440 25  13.041887   80.198871   2014-08-07 15:55:47     352944054874253 
441 25  13.041887   80.198871   2014-08-07 15:55:52     352944054874253 
442 25  13.041887   80.198871   2014-08-07 15:56:00     352944054874253

I have to compare rows having the same latitude and longitude values.

If previous row's latitude and longitude is the same as the next row's latitude and longitude, then calculate timestamp difference until the next row's latitude and longitude values aren't equal.

For example, from ID 438 to 442 the latitude and longitude values are the same so I have to calculate:

timediff = 442 received - 438 received time (in minutes)

and store it in table T2 like:

id  uid     latitude     longitude      idle_time               imno
1    25      13.041887   80.198871    2014-08-07 00:01:38    352944054874253 

start_time                        end_time
2014-08-07 15:55:38         2014-08-07 15:56:00

How can I solve this?

Best Answer

Supposing id is primary_key and auto increment column for both t1 and t2, I think this is what you need:

CREATE TRIGGER trig1
AFTER INSERT ON t1
FOR EACH ROW 
BEGIN
  IF @latitude = NEW.latitude AND @longitude = NEW.longitude THEN
     SET @end_time = NEW.received_time;
     SET @iddle_time = TIMEDIFF(NEW.received_time, @start_time);
     UPDATE t2 SET iddle_time = @iddle_time, end_time = @end_time
               WHERE id = @last_id_t2;
  ELSE

     INSERT INTO t2 VALUES (NULL, NEW.uid, NEW.latitude, NEW.longitude, 
                            NULL, NEW.imno, NEW.received_time, NEW.received_time);       
     SET @last_id_t2 = LAST_INSERT_ID();
     SET @latitude = NEW.latitude; SET @longitude = NEW.longitude;
     SET @iddle_time = NULL;
     SET @start_time = NEW.received_time; SET @end_time = NEW.received_time;
  END IF;
END;

You can see the whole scheme here : http://sqlfiddle.com/#!2/a70ac/1

Related Question