MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (`datetime` BIGINT, `index` VARCHAR(255));
INSERT INTO test VALUES
(1633378385000,4),
(1633378451000,4),
(1633378505000,4),
(1633378582000,4),
(1633378652000,4),
(1633378717000,4),
(1633378782000,''),
(1633378848000,''),
(1633378913000,''),
(1633383874000,''),
(1633383940000,5),
(1633384007000,5),
(1633384011000,5),
(1633384205000,5),
(1633384266000,5),
(1633384340000,5),
(1633387730000,''),
(1633387750000,''),
(1633388380000,4),
(1633389103000,4),
(1633389181000,4),
(1633389247000,4);
SELECT * FROM test;

Records: 22  Duplicates: 0  Warnings: 0




datetime
index


1633378385000
4

1633378451000
4

1633378505000
4

1633378582000
4

1633378652000
4

1633378717000
4

1633378782000


1633378848000


1633378913000


1633383874000


1633383940000
5

1633384007000
5

1633384011000
5

1633384205000
5

1633384266000
5

1633384340000
5

1633387730000


1633387750000


1633388380000
4

1633389103000
4

1633389181000
4

1633389247000
4


WITH 
cte1 AS ( SELECT *, `index` <> LAG(`index`) OVER (ORDER BY `datetime`) newgroup
          FROM test ),
cte2 AS ( SELECT *, COALESCE(SUM(newgroup) OVER (ORDER BY `datetime`), 0) groupnum
          FROM cte1 )
SELECT MIN(`datetime`) datetime_start,
       MAX(`datetime`) datetime_end,
       ANY_VALUE(`index`) `index`
FROM cte2
WHERE `index`
GROUP BY groupnum
ORDER BY 1;


datetime_start
datetime_end
index


1633378385000
1633378717000
4

1633383940000
1633384340000
5

1633388380000
1633389247000
4


WITH 
cte AS ( SELECT *,
                COALESCE(LAG(`index`) OVER (ORDER BY `datetime`), '') previousindex,
                COALESCE(LEAD(`index`) OVER (ORDER BY `datetime`), '') nextindex 
         FROM test )
SELECT `datetime`, `index`
FROM cte
WHERE '' IN (previousindex, nextindex)
 AND `index` <> '' 
ORDER BY 1;


datetime
index


1633378385000
4

1633378717000
4

1633383940000
5

1633384340000
5

1633388380000
4

1633389247000
4

datetime	index
1633378385000	4
1633378451000	4
1633378505000	4
1633378582000	4
1633378652000	4
1633378717000	4
1633378782000
1633378848000
1633378913000
1633383874000
1633383940000	5
1633384007000	5
1633384011000	5
1633384205000	5
1633384266000	5
1633384340000	5
1633387730000
1633387750000
1633388380000	4
1633389103000	4
1633389181000	4
1633389247000	4