Aide pour sensor sql statistique

Entraide Home Assistant
1

Dans le cadre de la réalisation d’une carte lovelace, pour le suivi des consommations, et des coûts de l’option TEMPO EDF, j’utilise les sensors sql afin d’aller chercher les valeurs à J, J-1, S, S-1,M ,M-1, A et A-1 et ce pour chaque tarifs HP bleu, HP blanc, HP rouge, HC bleu, HC blanc, et HC rouge.

Home assistant stocke 10 jours de statistiques dans la table states, puis il stocke les statistiques supérieures à 10 jours dans la table statistics , ainsi donc le problème est le suivant:
si je me trouve dans le cas de figure ou nous sommes un lundi, j’affiche pour (S-1) à partir du lundi précédent donc < 10jours je tape la table states, mais si nous sommes un samedi, je souhaite afficher la valeur du lundi précédent pour (S-1) soit 11 jours je dois taper la table statistics, j’ai donc pour la même requête, deux tables différentes à utiliser…

Ce problème se retrouve pour S-1 si >10jours, pour M si <10 jours, pour A si <10 jours

En utilisant CASE on peut vérifier avec (((strftime('%w', 'now') + 6) % 7 + 7) * 86400) + 21600 >= 864000 , que la durée en secondes depuis le lundi de la semaine précédente à 6h00 du matin est supérieure à 10 jours 864000 secondes, on peut selectionner la bonne table
mais dans la partie FROM je ne peux pas utiliser CASE et je sèche complétement…

Si l’un d’entre vous a une idée, merci d’avance.

SELECT
  CASE WHEN (((strftime('%w', 'now') + 6) % 7 + 7) * 86400) + 21600 >= 864000
        THEN statistics.state
        ELSE states.state
  END AS state
FROM
  statistics
  INNER JOIN statistics_meta ON
    statistics.metadata_id = statistics_meta.id

  states
  INNER JOIN states_meta ON
    states.metadata_id = states_meta.metadata_id

WHERE
    CASE WHEN (((strftime('%w', 'now') + 6) % 7 + 7) * 86400) + 21600 >= 864000
        THEN
          statistic_id =  'sensor.em_maison_bbrhpjr'
          AND strftime('%s', created_ts) <= strftime('%s', 'now') - (((strftime('%w', 'now') + 6) % 7 + 7) * 86400) + 21600 -
          CASE
          WHEN strftime('%m', created_ts) BETWEEN '04' AND '10' THEN 0
          ELSE 3600
          END
        ELSE
          states_meta.entity_id = 'sensor.em_maison_bbrhpjr'
          AND strftime('%s', 'last_updated_ts') <= strftime('%s', 'now') - (((strftime('%w', 'now') + 6) % 7) * 86400) + 21600 -
          CASE
          WHEN strftime('%m', last_updated_ts) BETWEEN '04' AND '10' THEN 0
          ELSE 3600
          END
    END
ORDER BY
  CASE WHEN (((strftime('%w', 'now') + 6) % 7 + 7) * 86400) + 21600 >= 864000
        THEN created_ts
        ELSE last_updated_ts
  END DESC
LIMIT
 1;
2

Tu fais deux requêtes et un UNION pour joindre tes deux requêtes. Par contre, tu es sûr que les données ne redescendent pas dans statistics immédiatement.

3

Il semblerait que non, j’ai utilisé sqliteweb pour inspecter, je n’ai pas trouvé de valeurs dans statistics pour le sensor dont j’ai besoin inferieures à 10 jours

pour UNION j’ai vu ca, j’ai essayé, mais je n’y suis pas parvenu.

4

Cela me semble bizarre, car je ne vois pas la logique de faire la consolidation au moment de la purge. Il faudrait que je regarde cela ce soir.

Pour l’UNION, bah je sais pas, normalement ça marche.

Tu as quoi comme erreur ? Elle ressemble à quoi ta requête avec l’UNION.

5

J’ai ca comme retour You can only execute one statement at a time.

      SELECT
        states.state
      FROM
        states
        INNER JOIN states_meta ON
          states.metadata_id = states_meta.metadata_id
      WHERE
        states_meta.entity_id = 'sensor.em_maison_bbrhpjr'
        AND last_updated_ts <=  strftime('%s', 'now') - (((strftime('%w', 'now') + 6) % 7 + 7) * 86400) - (strftime('%s', 'now') % 86400) +
      CASE
        WHEN strftime('%m', last_updated_ts) BETWEEN '04' AND '10' THEN 25200
        ELSE 18000
      END
      ORDER BY
        last_updated_ts DESC
      LIMIT
        1;
      UNION
      SELECT
        statistics.state
      FROM
        statistics
        INNER JOIN statistics_meta ON
          statistics.metadata_id = statistics_meta.id
      WHERE
        statistic_id =  'sensor.em_maison_bbrhpjr'
        AND created_ts <= strftime('%s', 'now') - (((strftime('%w', 'now') + 6) % 7 + 7) * 86400) - (strftime('%s', 'now') % 86400) +
      CASE
        WHEN strftime('%m', created_ts) BETWEEN '04' AND '10' THEN 25200
        ELSE 18000
      END
      ORDER BY
        created_ts DESC
      LIMIT
        1;
6

Il ne faut pas de « ; » avant le UNION. C’est une unique requête.

7

ok merci pour ca, maintenant j’ai ORDER BY clause should come after UNION not before donc je suppose que comme c’est une unique requête il ne faut qu’un seul ORDER BY

8

Le message est clair … Il t’indique que tu ne peux pas faire de ORDER BY au milieu de ta requête :

Donc, il faut faire un truc du style :

SELECT <monchamp> FROM (

SELECT <monchamp1> FROM <matable1>
WHERE <condition1>

UNION

SELECT <monchamp2> FROM <matable2>
WHERE <condition2>

) ORDER BY <monchamp>

Enfin un truc du style …

Un peu de documentation : SELECT (§ 3. Compound Select Statements)

9

et c’est la que réside le problème c’est qu’il n’y a pas de champ commun pour le order by

10

Si tu veux faire un union qui mélange des carottes et des navets, effectivement c’est un problème … sauf si tu renommes ton champs en « légume » … via le mot clef « AS » …

Dans la documentation, il faut regarder du côté de result-column :grinning:

SELECT <monchamp2> AS monlegume FROM <matable2>
WHERE <condition2>

Bon en réalité, si tu utilises le nom du champs du premier SELECT dans le UNION ça devrait aussi le faire.

Joue ta requête sans le ORDER BY et regarde le nom du champ qu’il te renvoie.

1 « J'aime »
11

ok je vois d’avancer la dessus, et je reviens vers toi, merci pour ton retour.

12

Ben pour le coup grâce à tes infos je pense être bon, à vérifier pour le coup dans les prochains jours, je donnerais l’info, quoi qu’il en soit merci beaucoup.

SELECT
       state, last_updated_ts AS Timestamp
       FROM
        states
        INNER JOIN states_meta ON
          states.metadata_id = states_meta.metadata_id
      WHERE
        states_meta.entity_id = 'sensor.em_maison_bbrhpjb'
        AND Timestamp <= strftime('%s', 'now') - (((strftime('%w', 'now') + 6) % 7 + 7) * 86400) - (strftime('%s', 'now') % 86400) +
      CASE
        WHEN strftime('%m', Timestamp) BETWEEN '04' AND '10' THEN 25200
        ELSE 18000
      END
UNION ALL
SELECT
       state, created_ts AS Timestamp
       FROM
        statistics
        INNER JOIN statistics_meta ON
          statistics.metadata_id = statistics_meta.id
      WHERE
        statistic_id =  'sensor.em_maison_bbrhpjb'
        AND Timestamp <= strftime('%s', 'now') - (((strftime('%w', 'now') + 6) % 7 + 7) * 86400) - (strftime('%s', 'now') % 86400) +
      CASE
        WHEN strftime('%m', Timestamp) BETWEEN '04' AND '10' THEN 25200
        ELSE 18000
      END
ORDER BY Timestamp DESC