MariaDB query problem

hello,
well never used a SQL db before… so bare with me…
here is my problem…
i’ll using the MariaDB now … sucks…
needed some time to find out that you have to use “” for the URL … all little strange to me…


  - platform: sql
    db_url: "mysql://homeassistant:XXX@core-mariadb/homeassistant?charset=utf8mb4"
    queries:
      - name: Bresser51 DB Rain 1H
        query: >-
          SELECT * FROM states
          WHERE entity_id = 'sensor.bresser51_rain'
                AND last_updated < CURRENT_TIMESTAMP - INTERVAL '1 hour'
          ORDER BY last_updated DESC
          LIMIT 1;
        column: state
        unit_of_measurement: mm
      - name: Bresser51 DB Rain 24H
        query: >-
          SELECT * FROM states
          WHERE entity_id = 'sensor.bresser51_rain'
                AND last_updated < CURRENT_TIMESTAMP - INTERVAL '24 hour'
          ORDER BY last_updated DESC
          LIMIT 1;
        column: state
        unit_of_measurement: mm
      - name: Bresser51 DB Rain 168H
        query: >-
          SELECT * FROM states
          WHERE entity_id = 'sensor.bresser51_rain'
                AND last_updated < CURRENT_TIMESTAMP - INTERVAL '168 hour'
          ORDER BY last_updated DESC
          LIMIT 1;
        column: state
        unit_of_measurement: mm

can’t get it to work… did not found any example for MariaDB … only how to find out the size of the db
also use the !secret for URL & Pass… this is just for testing…

Never mind that the Url to db must be in " " … nothing in documentation about it …
maybe use a other SQL DB ? …

don’t know how the query works with MariaDB… if someone could help me would be awesome …

many thx
M.

Many things are wrong here:

  1. last_updated is stored in UTC in MariaDB, so you’ll most probably have to convert your timestamps in your local time zone.
  2. To do local conversion, you need the timezone table loaded in MariaDB
    mysql_tzinfo_to_sql - MariaDB Knowledge Base
  3. You don’t need to quote your interval, the correct syntax is
    last_updated < now() - INTERVAL 24 hour
  4. Are you sure that you want a “less than” in your queries ?
  5. You can put the whole db url in secret, that’s what I did and can use it in my sensors no need for quotes either
    hadb: mysql://homeassistant:[email protected]/homeassistant?charset=utf8mb4

About (4), if you want to know the latest recorded value from when it was 1 hour ago, 1 day ago or 1 week ago then your conditions are right.
If you want to know the min, max or mean of the periods then it is not right.
But you’re the one that knows.

You probably have phpMyAdmin installed to manage your DB, use it to test your query part before using them in HA.

1 Like

Your queries need to be adapted too, you are not aggregating any data and SQL will then only kick back one of those values
EDIT, ah no…you have LIMIT1

Yes, but I don’t know the use of those queries.
It should probably be >= with min, max, avg and a group by.
But who knows ? Maybe this is what @MadMac wants, the latest value of 1 hour, 1 day and 1 week ago.

Yep… might help to understand what the OP wants :slight_smile:

last_updated < now() - INTERVAL 24 hour

YES …
had the rest already… those Syntax drive me crazy :wink:
many thx for quick help!
M.

1 Like