By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tablename (`species` TEXT, `timestamp` TEXT);
✓
INSERT INTO tablename (`species`, `timestamp`) VALUES
('A', '2021-04-01T12:35'),
('A', '2021-04-01T12:36'),
('B', '2021-04-01T12:36'),
('A', '2021-04-01T12:37'),
('A', '2021-04-01T12:39'),
('C', '2021-04-01T12:40'),
('B', '2021-04-01T12:42'),
('C', '2021-04-01T12:44'),
('C', '2021-04-01T12:44'),
('B', '2021-04-01T12:47'),
('A', '2021-04-01T12:56'),
('B', '2021-04-01T13:00');
✓
SELECT DISTINCT
strftime(
'%Y-%m-%dT%H:%M',
timestamp,
'-' || CASE WHEN timestamp LIKE '%0' THEN 9 ELSE SUBSTR(timestamp, -1) - 1 END || ' minute'
) chunk_start_time,
strftime(
'%Y-%m-%dT%H:%M',
timestamp,
'+' || CASE WHEN timestamp LIKE '%0' THEN 0 ELSE 10 - SUBSTR(timestamp, -1) END || ' minute'
) chunk_end_time,
species
FROM tablename
ORDER BY chunk_start_time, species;
| chunk_start_time | chunk_end_time | species |
|---|---|---|
| 2021-04-01T12:31 | 2021-04-01T12:40 | A |
| 2021-04-01T12:31 | 2021-04-01T12:40 | B |
| 2021-04-01T12:31 | 2021-04-01T12:40 | C |
| 2021-04-01T12:41 | 2021-04-01T12:50 | B |
| 2021-04-01T12:41 | 2021-04-01T12:50 | C |
| 2021-04-01T12:51 | 2021-04-01T13:00 | A |
| 2021-04-01T12:51 | 2021-04-01T13:00 | B |