galmok
July 22, 2023, 3:10pm
1
I would like to clean up my db as some entities have logged invalid values as true values (specific value should be taken as invalid value and not as a reading).
I found no way to do it via the GUI, so I am looking the database itself:
select *
from states
where metadata_id = 206 and state = '281474976710.655'
(206 is the entity as found in states_meta)
This gives me the bad readings that I would like to delete. But attempting to do so gives a foreign key violation (doesn’t state which). There are 3 foreign keys and 1 of them is self-referencing(!).
How can I delete these measurements (and only those)?
And afterwards, I would need to fix the statistics as well I guess. Can I do that using the GUI or what?
1 Like
skrobul
December 20, 2024, 12:37pm
2
Late to the game, but since this is showing up as one of the first results in search engines, here’s an example of how can this be done:
In a transaction, set the old_state_id on all rows that you want to remove to be null.
Identify the record that is immediately before the rows that you are deleting. In my case, this was 85180622.
Identify the record that is immediately after the rows that are being deleted. In my case this was 85180622. Update that record’s old_state_id column to the value identified in a previous step.
Delete the rows.
hassdb=# begin;
BEGIN
hassdb=*# select state,last_updated_ts,old_state_id from states where metadata_id=1478 and last_updated_ts > 1734638096.7943115 and last_updated_ts<1734640057.1463976;
state | last_updated_ts | old_state_id
-------+--------------------+--------------
7 | 1734638334.6322927 | 85180622
8 | 1734638334.9476323 | 85180720
9 | 1734638352.613414 | 85180724
10 | 1734638353.630708 | 85180732
11 | 1734638355.938325 | 85180738
12 | 1734638356.6594486 | 85180743
13 | 1734638364.9985752 | 85180750
14 | 1734638365.4671695 | 85180761
15 | 1734638366.0161312 | 85180766
16 | 1734638368.9796796 | 85180774
17 | 1734638369.4127283 | 85180780
18 | 1734638370.820492 | 85180783
19 | 1734638373.022636 | 85180786
20 | 1734638373.4731393 | 85180789
21 | 1734638376.398571 | 85180792
22 | 1734638376.883189 | 85180800
23 | 1734638387.3796175 | 85180803
24 | 1734638387.832865 | 85180811
25 | 1734638394.1126964 | 85180817
26 | 1734638394.502871 | 85180824
27 | 1734638395.9446988 | 85180827
28 | 1734638396.4090393 | 85180834
29 | 1734638398.4352806 | 85180837
30 | 1734638399.1677434 | 85180840
(24 rows)
hassdb=*# select state_id,state,last_updated_ts,old_state_id from states where metadata_id=1478 and last_updated_ts > 1734638096.7943115 and last_updated_ts<1734640057.1463976;
state_id | state | last_updated_ts | old_state_id
----------+-------+--------------------+--------------
85180720 | 7 | 1734638334.6322927 | 85180622
85180724 | 8 | 1734638334.9476323 | 85180720
85180732 | 9 | 1734638352.613414 | 85180724
85180738 | 10 | 1734638353.630708 | 85180732
85180743 | 11 | 1734638355.938325 | 85180738
85180750 | 12 | 1734638356.6594486 | 85180743
85180761 | 13 | 1734638364.9985752 | 85180750
85180766 | 14 | 1734638365.4671695 | 85180761
85180774 | 15 | 1734638366.0161312 | 85180766
85180780 | 16 | 1734638368.9796796 | 85180774
85180783 | 17 | 1734638369.4127283 | 85180780
85180786 | 18 | 1734638370.820492 | 85180783
85180789 | 19 | 1734638373.022636 | 85180786
85180792 | 20 | 1734638373.4731393 | 85180789
85180800 | 21 | 1734638376.398571 | 85180792
85180803 | 22 | 1734638376.883189 | 85180800
85180811 | 23 | 1734638387.3796175 | 85180803
85180817 | 24 | 1734638387.832865 | 85180811
85180824 | 25 | 1734638394.1126964 | 85180817
85180827 | 26 | 1734638394.502871 | 85180824
85180834 | 27 | 1734638395.9446988 | 85180827
85180837 | 28 | 1734638396.4090393 | 85180834
85180840 | 29 | 1734638398.4352806 | 85180837
85180848 | 30 | 1734638399.1677434 | 85180840
(24 rows)
hassdb=*# select state_id,state,last_updated_ts,old_state_id from states where metadata_id=1478;
state_id | state | last_updated_ts | old_state_id
----------+-------+--------------------+--------------
85166637 | 0 | 1734606678.6816583 |
85167914 | 1 | 1734609561.915644 | 85166637
85169555 | 2 | 1734613147.60249 | 85167914
85172805 | 2 | 1734621586.919581 |
85173841 | 2 | 1734621756.5170276 |
85177667 | 3 | 1734629819.3852065 | 85173841
85180506 | 4 | 1734637723.1795979 | 85177667
85180515 | 5 | 1734637723.5025015 | 85180506
85180622 | 6 | 1734638096.7943115 | 85180515
85180732 | 9 | 1734638352.613414 | 85180724
85180720 | 7 | 1734638334.6322927 | 85180622
85180724 | 8 | 1734638334.9476323 | 85180720
85180743 | 11 | 1734638355.938325 | 85180738
85180750 | 12 | 1734638356.6594486 | 85180743
85180761 | 13 | 1734638364.9985752 | 85180750
85180766 | 14 | 1734638365.4671695 | 85180761
85180774 | 15 | 1734638366.0161312 | 85180766
85180800 | 21 | 1734638376.398571 | 85180792
85180803 | 22 | 1734638376.883189 | 85180800
85180848 | 30 | 1734638399.1677434 | 85180840
85180738 | 10 | 1734638353.630708 | 85180732
85180780 | 16 | 1734638368.9796796 | 85180774
85180783 | 17 | 1734638369.4127283 | 85180780
85180786 | 18 | 1734638370.820492 | 85180783
85180789 | 19 | 1734638373.022636 | 85180786
85180792 | 20 | 1734638373.4731393 | 85180789
85180811 | 23 | 1734638387.3796175 | 85180803
85180817 | 24 | 1734638387.832865 | 85180811
85180824 | 25 | 1734638394.1126964 | 85180817
85180827 | 26 | 1734638394.502871 | 85180824
85180834 | 27 | 1734638395.9446988 | 85180827
85180837 | 28 | 1734638396.4090393 | 85180834
85180840 | 29 | 1734638398.4352806 | 85180837
85181587 | 0 | 1734640057.1463976 | 85180848
85181764 | 1 | 1734640590.0282643 | 85181587
85182203 | 3 | 1734641709.9987211 | 85182200
85182210 | 4 | 1734641713.6410496 | 85182203
85182200 | 2 | 1734641708.300092 | 85181764
85182396 | 5 | 1734642109.063395 | 85182210
85196127 | 6 | 1734685559.066882 | 85182396
85197598 | 7 | 1734689026.030945 | 85196127
(41 rows)
hassdb=*# update states set old_state_id=null where metadata_id=1478 and last_updated_ts > 1734638096.7943115 and last_updated_ts<1734640057.1463976;
UPDATE 24
hassdb=*# update states set old_state_id=85180622 where state_id=85181587;
UPDATE 1
hassdb=*# select state_id,state,last_updated_ts,old_state_id from states where metadata_id=1478;
state_id | state | last_updated_ts | old_state_id
----------+-------+--------------------+--------------
85166637 | 0 | 1734606678.6816583 |
85167914 | 1 | 1734609561.915644 | 85166637
85169555 | 2 | 1734613147.60249 | 85167914
85172805 | 2 | 1734621586.919581 |
85173841 | 2 | 1734621756.5170276 |
85177667 | 3 | 1734629819.3852065 | 85173841
85180506 | 4 | 1734637723.1795979 | 85177667
85180515 | 5 | 1734637723.5025015 | 85180506
85180622 | 6 | 1734638096.7943115 | 85180515
85180732 | 9 | 1734638352.613414 |
85180720 | 7 | 1734638334.6322927 |
85180724 | 8 | 1734638334.9476323 |
85180743 | 11 | 1734638355.938325 |
85180750 | 12 | 1734638356.6594486 |
85180761 | 13 | 1734638364.9985752 |
85180738 | 10 | 1734638353.630708 |
85180780 | 16 | 1734638368.9796796 |
85180783 | 17 | 1734638369.4127283 |
85180837 | 28 | 1734638396.4090393 |
85180840 | 29 | 1734638398.4352806 |
85181587 | 0 | 1734640057.1463976 | 85180622
85181764 | 1 | 1734640590.0282643 | 85181587
85182203 | 3 | 1734641709.9987211 | 85182200
85182210 | 4 | 1734641713.6410496 | 85182203
85182200 | 2 | 1734641708.300092 | 85181764
85182396 | 5 | 1734642109.063395 | 85182210
85196127 | 6 | 1734685559.066882 | 85182396
85197598 | 7 | 1734689026.030945 | 85196127
85180766 | 14 | 1734638365.4671695 |
85180774 | 15 | 1734638366.0161312 |
85180786 | 18 | 1734638370.820492 |
85180789 | 19 | 1734638373.022636 |
85180792 | 20 | 1734638373.4731393 |
85180800 | 21 | 1734638376.398571 |
85180803 | 22 | 1734638376.883189 |
85180811 | 23 | 1734638387.3796175 |
85180817 | 24 | 1734638387.832865 |
85180824 | 25 | 1734638394.1126964 |
85180827 | 26 | 1734638394.502871 |
85180834 | 27 | 1734638395.9446988 |
85180848 | 30 | 1734638399.1677434 |
(41 rows)
hassdb=*# delete from states where metadata_id=1478 and last_updated_ts > 1734638096.7943115 and last_updated_ts<1734640057.1463976;
DELETE 24
hassdb=*# select state_id,state,last_updated_ts,old_state_id from states where metadata_id=1478;
state_id | state | last_updated_ts | old_state_id
----------+-------+--------------------+--------------
85166637 | 0 | 1734606678.6816583 |
85167914 | 1 | 1734609561.915644 | 85166637
85169555 | 2 | 1734613147.60249 | 85167914
85172805 | 2 | 1734621586.919581 |
85173841 | 2 | 1734621756.5170276 |
85177667 | 3 | 1734629819.3852065 | 85173841
85180506 | 4 | 1734637723.1795979 | 85177667
85180515 | 5 | 1734637723.5025015 | 85180506
85180622 | 6 | 1734638096.7943115 | 85180515
85181587 | 0 | 1734640057.1463976 | 85180622
85181764 | 1 | 1734640590.0282643 | 85181587
85182203 | 3 | 1734641709.9987211 | 85182200
85182210 | 4 | 1734641713.6410496 | 85182203
85182200 | 2 | 1734641708.300092 | 85181764
85182396 | 5 | 1734642109.063395 | 85182210
85196127 | 6 | 1734685559.066882 | 85182396
85197598 | 7 | 1734689026.030945 | 85196127
(17 rows)
hassdb=*# commit;
COMMIT
hassdb=#
As always - BACKUP your database first.
1 Like
galmok
December 20, 2024, 1:41pm
3
This looks good. Thank you.
Do you also have a way to recalculate the statistics?