title: Grouping 'islands' in MySQL 5.7
body: <p>I'm struggling to solve a 'gaps and island' problem that I thought would be simple.</p>
  <p>Turn this result set:</p>
  <pre><code>7:18    WORK
  7:19    WORK
  7:20    WORK
  7:29    BREAK
  7:30    BREAK
  7:31    WORK
  7:32    WORK
  7:33    IDLE
  7:34    IDLE
  7:35    IDLE
  7:36    IDLE
  </code></pre>
  <p>To this:</p>
  <pre><code>from_timestamp  until_timestamp status  records_in_island
  7:18    7:24    WORK    6
  7:29    7:32    BREAK   3
  7:31    7:32    WORK    2
  7:33    7:36    IDLE    4
  </code></pre>
  <p>Or basically to show:</p>
  <ul>
  <li>timestamp of the start and end of each island</li>
  <li>number of records in each island</li>
  </ul>
  <p>Here's a dbfiddle with the data:
  <a href="https://dbfiddle.uk/?rdbms=mysql_5.7&amp;fiddle=a15cc8325ef4bc743df3208f8f328b9a" rel="nofollow noreferrer">https://dbfiddle.uk/?rdbms=mysql_5.7&amp;fiddle=a15cc8325ef4bc743df3208f8f328b9a</a></p>
  <p>I couldn't find any solution in the gaps and islands solutions I found. I'm using MySQL 5.7 (due to AWS Aurora limitation) so can't use WITH or other MySQL 8.0 syntax.</p>
  <p>Any help would be hugely appreciated.</p>