Invalid state of some sensor: how to check it in DB?

Configuration
1

Here is some template sensor:

        state: >-
          {{ states("sensor.xiaomi_cg_1") }}
        availability: >-
          {{ not states("sensor.xiaomi_cg_1") in ["unavailable","unknown"] and
             not is_state("sensor.xiaomi_cg_1",99999) }}

It takes a state of some source sensor (Xiaomi Cleargrass Air monitor).
Why using this “99999” filter?
A couple of years ago a source sensor’s state was “99999” - so this was an attempt to filter these spikes.

Yesterday I again noticed this:

изображение
изображение1873×563 37.6 KB

Now I suspect that my filter is wrong.
Two questions:

  1. What could be a problem with my filter?
    Update: probably I should check for a “99999” string too…

  2. How can I access data from DB? (a total noob in SQL). Installed MySQL workbench. Probably there is a way to get this value by some SQL query.

HA in Docker. So not sure if DB is exposed for external access.

2

states are strings, you’re comparing to a number

edit, see your edit

3

Petro, I found an interesting issue:

  1. To check for a invalid attribute value - I need to compare to 99999 (number).
  2. To check for a invalid state value - I need to compare to “99999” (string).

Why I think so?
First I observed this 9999-issue with a PM2.5 (a state) in year 2021.
I added a check for “99999” (string) for all template sensors from this device (pm25 taken from state & others taken from attributes - co2, tvoc, temperature, humidity).
And there were no 99999-spikes for PM25 since then.

Recently (February 2023) there were spikes for “Temperature” & “Humidity”.
And that check for “99999”-string did not work.
Checked again in Dev tools (manually set 99999 values for “Temperature” & “Humidity” attributes) - and my string filter did NOT work.
Tried with a 99999-number filter - it worked.
So I replaced all my “99999”-string filters with 99999-number filters.
And now I think that for “PM25” (taken from a state) I should keep a “99999”-string filter.

Could it be correct?

4

That’s not really an issue. That’s how attributes work. The main state in HA is always a string. Attributes can be any native python type.

5

Thank you so much.
By “issue” I meant a “question”, “point” (wrong translation from Russian).

As for editing a wrong value in DB - I am thinking about not the best but working variant:

  1. Stop HA container.
  2. Move db-file to some shared folder.
  3. Move db-file from this shared folder on some Windows-machine.
  4. Open db-file in SQLite Expert.
  5. Open a table with states.
  6. Filter a “state” column with “99999” value (found 1 record with an expected timestamp).
  7. Manually edit a “state” value.
  8. Move db-file back to it’s place.
  9. Start HA.

This is not a statistical data, so I could just wait for 10 days…

Same method was also used to fix statistics.