With a MySQL table with timestamps.
|Timestamps |
|-----------------------|
|2021-08-01 14:00:00.000|
|2021-08-01 14:00:00.100|
|2021-08-01 14:00:00.200|
|2021-08-01 14:00:00.300|
|2021-08-01 14:00:00.600|
|2021-08-01 14:00:00.700|
|2021-08-01 14:00:00.800|
|2021-08-01 14:00:01.000|
I would like to get the time intervals of the continuous data. Continuous is defined by a frequency, in this example, it can be 10Hz The desired result would be
|Start | End |
|------------------------|------------------------|
|2021-08-01 14:00:00.000 | 2021-08-01 14:00:00.300|
|2021-08-01 14:00:00.600 | 2021-08-01 14:00:00.800|
I am using MySQL version 5.7.35 and cant use WITH and other functions. Can this be done fast? There are ca. 100000 elements per table for now.
Use :
CREATE TABLE test_tbl (
my_data timestamp(3)
);
INSERT INTO test_tbl VALUES
('2021-08-01 14:00:00.000'),
('2021-08-01 14:00:00.100'),
('2021-08-01 14:00:00.200'),
('2021-08-01 14:00:00.300'),
('2021-08-01 14:00:00.600'),
('2021-08-01 14:00:00.700'),
('2021-08-01 14:00:00.800'),
('2021-08-01 14:00:01.000');
SELECT
MIN(my_data) start,
MAX(my_data) end
FROM
( SELECT *
, CASE WHEN right(my_data,3) = @prev+100 THEN @i:=@i ELSE @i:=@i+100 END row_num
, @prev:=right(my_data,3) prev
FROM test_tbl
, (SELECT @prev:= null,@i:=0) vars
ORDER BY row_num
) x
GROUP BY row_num;