By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE events_table (event_id TEXT, start_time TEXT);
✓
INSERT INTO events_table (event_id, start_time) VALUES
('event_1', '1999-01-01 16:17:28'),
('event_1', '1999-01-01 16:17:30'),
('event_1', '1999-01-01 18:16:15'),
('event_1', '1999-01-02 12:02:27'),
('event_1', '1999-01-02 12:02:29'),
('event_1', '1999-01-03 08:53:59'),
('event_1', '1999-01-04 17:35:59'),
('event_1', '1999-01-05 11:18:24'),
('event_1', '1999-01-05 11:31:34'),
('event_1', '1999-01-05 19:54:56'),
('event_1', '1999-01-05 21:42:47'),
('event_4', '1999-01-07 15:15:06'),
('event_6', '1999-01-07 18:26:38'),
('event_6', '1999-01-07 18:27:32'),
('event_6', '1999-01-07 18:29:23'),
('event_6', '1999-01-07 20:00:33'),
('event_6', '1999-01-07 20:40:16'),
('event_2', '1999-01-07 21:02:34'),
('event_2', '1999-01-08 16:12:34'),
('event_2', '1999-01-08 18:36:21'),
('event_2', '1999-01-08 21:37:26'),
('event_2', '1999-01-10 13:13:08'),
('event_2', '1999-01-10 19:10:42'),
('', '1999-01-11 10:11:11'),
('', '1999-01-11 13:22:31'),
('', '1999-01-11 13:23:36'),
('', '1999-01-11 13:35:08'),
('', '1999-01-11 16:05:35'),
('', '1999-01-11 16:12:09'),
('', '1999-01-18 08:22:10'),
('event_4', '1999-01-18 08:45:59'),
('event_7', '1999-01-18 09:26:55'),
('event_7', '1999-01-18 09:29:22'),
('event_7', '1999-01-18 09:46:07'),
('event_7', '1999-01-18 10:46:17'),
✓
WITH cte AS (
SELECT *, event_id IS NOT LAG(event_id) OVER (PARTITION BY date(start_time) ORDER BY start_time) flag
FROM events_table
WHERE event_id <> ''
)
SELECT DISTINCT date(start_time) AS day,
GROUP_CONCAT(event_id, ' | ') OVER (
PARTITION BY date(start_time)
ORDER BY start_time
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS events
FROM cte
WHERE flag AND start_time BETWEEN '1999-01-01 00:00:00' AND '1999-03-31 23:59:59';
| day | events |
|---|---|
| 1999-01-01 | event_1 |
| 1999-01-02 | event_1 |
| 1999-01-03 | event_1 |
| 1999-01-04 | event_1 |
| 1999-01-05 | event_1 |
| 1999-01-07 | event_4 | event_6 | event_2 |
| 1999-01-08 | event_2 |
| 1999-01-10 | event_2 |
| 1999-01-18 | event_4 | event_7 | event_2 | event_7 | event_2 | event_7 | event_2 |
| 1999-01-19 | event_2 |
| 1999-01-20 | event_2 |
| 1999-01-21 | event_2 |
| 1999-01-22 | event_1 |
| 1999-01-25 | event_6 | event_2 |
| 1999-01-26 | event_2 |
| 1999-01-27 | event_2 |
| 1999-01-28 | event_2 |
| 1999-01-29 | event_2 |
| 1999-01-30 | event_2 |
| 1999-01-31 | event_2 |
| 1999-02-01 | event_2 |
| 1999-02-02 | event_2 |
| 1999-02-03 | event_2 |
| 1999-02-04 | event_2 |
| 1999-02-05 | event_2 |
| 1999-02-06 | event_2 |
| 1999-02-07 | event_2 |
| 1999-02-08 | event_2 |
| 1999-02-09 | event_3 | event_1 |
| 1999-02-10 | event_1 |
| 1999-02-11 | event_1 |
| 1999-02-12 | event_1 |
| 1999-02-13 | event_2 |
| 1999-02-14 | event_4 | event_6 | event_2 |
| 1999-02-15 | event_2 |
| 1999-02-16 | event_2 |
| 1999-02-17 | event_2 |
| 1999-02-22 | event_2 |
| 1999-02-23 | event_4 | event_2 |
| 1999-02-24 | event_4 | event_6 | event_2 |
| 1999-02-25 | event_7 | event_1 |
| 1999-02-26 | event_1 |
| 1999-02-27 | event_2 |
| 1999-02-28 | event_4 | event_7 | event_4 | event_2 |
| 1999-03-01 | event_2 | event_7 | event_2 |
| 1999-03-02 | event_6 | event_2 |
| 1999-03-03 | event_2 |
| 1999-03-04 | event_2 |
| 1999-03-07 | event_2 |
| 1999-03-08 | event_2 |
| 1999-03-09 | event_2 |
| 1999-03-11 | event_2 |
| 1999-03-12 | event_2 | event_1 |
| 1999-03-13 | event_2 |
| 1999-03-14 | event_2 |
| 1999-03-15 | event_6 | event_2 |
| 1999-03-16 | event_2 |
| 1999-03-17 | event_4 | event_2 |
| 1999-03-18 | event_6 | event_2 |
| 1999-03-19 | event_2 |
| 1999-03-20 | event_2 |
| 1999-03-21 | event_4 | event_2 |
| 1999-03-22 | event_2 | event_7 |
| 1999-03-23 | event_4 | event_2 |
| 1999-03-24 | event_2 |
| 1999-03-26 | event_2 |
| 1999-03-27 | event_2 |
| 1999-03-28 | event_7 | event_2 |
| 1999-03-29 | event_2 |
| 1999-03-30 | event_4 | event_2 |
| 1999-03-31 | event_2 |