How can I extract the start and end of multiple ranges from a column?-CodePudding

I have a database where a column called StatusMotor is so:

                               
  Date-time        MotorStatus 
                               
  03-02-20 18:35   Start         
  03-02-20 18:35   Start       
  03-02-20 18:36   Start       
  03-02-20 18:35   Start       
  03-02-20 18:36   Start       
  03-02-20 18:36   Start       
  03-02-20 18:36   Stop        
  03-02-20 18:36   Stop        
  03-02-20 18:36   Stop        
  03-02-20 18:36   Standby      
  03-02-20 18:37   Standby     
  03-02-20 18:37   Start       
  ...              ....

I have three status (START, STOP, STAND BY) and i would extract moments when Motor works:

the date-time when i have 1st Start or Start after a Stop and Standby, and when I have last start before a Stop or Standby.

select DateTime, MotorStatus
from TableName 
Where MotorStatus like 'Start' and...

I don't know what condition i put to have this range. How i could to do?

CodePudding user response：

This is a gaps and islands problem, and on MySQL 8 we can use the difference in row numbers method here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY DateTime) rn1,
              ROW_NUMBER() OVER (PARTITION BY MotorStatus ORDER BY DateTime) rn2
    FROM yourTable
)

SELECT MIN(DateTime) AS start, MAX(DateTime) AS `end`
FROM cte
WHERE MotorStatus = 'Start'
GROUP BY rn1 - rn2
ORDER BY start;

Demo