Unable to generate a MySQL query

Question

I have a table in a MySQL database as below:

ID  Timestamp           State
1   2013-07-10 09:00:00 Good
1   2013-07-10 09:10:00 Good
1   2013-07-10 09:20:00 Good
1   2013-07-10 09:30:00 Bad
1   2013-07-10 09:40:00 Bad
1   2013-07-10 09:50:00 Bad
1   2013-07-10 10:00:00 Good
1   2013-07-10 10:10:00 Good

I want to generate a report as shown below using the above table.

ID  Start Timestamp         End Timestamp        Duration(in mins)  State
1   2013-07-10 09:00:00 2013-07-10 09:30:00 30          Good
1   2013-07-10 09:30:00 2013-07-10 10:00:00 30          Bad
1   2013-07-10 10:00:00 2013-07-10 10:10:00 10          Good

The query that I had generated is not able to showcase the switching taking place.

Answer 1

One way to do this is using user variables. The following code will sort through the table (first in reverse) and then through this result set (grouping and) keeping only rows that are wanted:

SELECT 
    id, 
    MIN(time_stamp) AS start_timestamp, 
    start_ts        AS end_timestamp,
    TIMESTAMPDIFF(MINUTE, MIN(time_stamp), start_ts)
                    AS duration_in_mins,
    MIN(state)      AS state
FROM    
  ( SELECT 
        id, time_stamp, state,
        @st_ts :=
            CASE WHEN state = @prev_state AND id = @prev_id
                     THEN @st_ts 
                 WHEN id = @prev_id THEN @prev_ts 
                     ELSE time_stamp
            END AS start_ts, 
        @prev_ts := time_stamp,
        @prev_state := state,
        @prev_id := id
    FROM sample
       CROSS JOIN
         ( SELECT 
             @prev_state := '',  @prev_ts := 0,
             @st_ts := 0,        @prev_id := 0 
         ) AS dummy 
    ORDER BY id DESC, time_stamp DESC
  ) AS tmptable 
GROUP BY 
    id, start_ts ;

Test at SQL-Fiddle-1