Get event duration

Question

MySQL

Good evening! I have a table:

id	time	type_event
one	2013-03-25 02:00:02	one
2	2013-03-25 02:30:22	one
3	2013-03-25 02:30:23	0
4	2013-03-25 02:50:22	0
5	2013-03-25 02:50:25	one

Is it possible by means of mysql to select events grouped by time and duration of the event? for example, in this table, when selecting, if possible, there will be 3 records:

id	time_start	duration (sec.)	type_event
one	2013-03-25 02:00:02	1820	one
2	2013-03-25 02:30:23	1199	0
3	2013-03-25 02:50:25	0	one

Is it possible? Thank you very much for the advice!

Answer 1

If the events follow strictly, then:

SELECT @id:=0;
SELECT 
  @id:[email protected]id+1 as id,
  A.`time`, 
  IFNULL(UNIX_TIMESTAMP(A2.`time`)-UNIX_TIMESTAMP(A.`time`), 0) as dura, 
  A.type
FROM A
LEFT JOIN A A2
ON A.id + 1 = A2.id
WHERE
  MOD(A.id, 2) = 1 
  AND A.`time` BETWEEN '2013-01-01' AND '2014-01-01';