SQLite 3.27

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

CREATE TABLE states (
  state_id INTEGER PRIMARY KEY, 
  entity_id INTEGER, 
  last_changed TEXT
);
✓
INSERT INTO states (entity_id, last_changed) VALUES
  (1, '2022-05-01 10:00:05'), (1, '2022-05-01 10:00:10'), (1, '2022-05-01 10:00:15'), 
  (2, '2022-05-01 11:00:20'), (2, '2022-05-01 11:00:30'), (2, '2022-05-01 11:00:55'), 
  (3, '2022-05-01 17:00:25'), (3, '2022-05-01 17:00:45'), (3, '2022-05-01 17:00:55');
✓
SELECT * FROM states;


state_id
entity_id
last_changed


1
1
2022-05-01 10:00:05

2
1
2022-05-01 10:00:10

3
1
2022-05-01 10:00:15

4
2
2022-05-01 11:00:20

5
2
2022-05-01 11:00:30

6
2
2022-05-01 11:00:55

7
3
2022-05-01 17:00:25

8
3
2022-05-01 17:00:45

9
3
2022-05-01 17:00:55


DELETE FROM states 
WHERE (entity_id, last_changed) NOT IN (
  SELECT entity_id, MAX(last_changed)
  FROM states 
  GROUP BY entity_id, strftime('%Y-%m-%d% %H', last_changed) 
);
✓
SELECT * FROM states;


state_id
entity_id
last_changed


3
1
2022-05-01 10:00:15

6
2
2022-05-01 11:00:55

9
3
2022-05-01 17:00:55

state_id	entity_id	last_changed
1	1	2022-05-01 10:00:05
2	1	2022-05-01 10:00:10
3	1	2022-05-01 10:00:15
4	2	2022-05-01 11:00:20
5	2	2022-05-01 11:00:30
6	2	2022-05-01 11:00:55
7	3	2022-05-01 17:00:25
8	3	2022-05-01 17:00:45
9	3	2022-05-01 17:00:55