Hi all, i need to create a query for Grafana to show daily consumpion of mi Heath Pump. For now, i created a query (it’s working) to display all consumption (DHW , HP, and DEFROST all togheter).
I need to separate the consumption for DHW HP and more…
The master query is:
SELECT mean(“mean”)*24 FROM (SELECT mean(“value”) FROM “KWh” WHERE (“entity_id” = ‘pdc_pt_in_w’) AND $timeFilter GROUP BY time(5s) fill(previous) tz(‘Europe/Rome’) ) GROUP BY time(1d) fill(0) tz(‘Europe/Rome’)
I need to fetch the data in same range only if this query results is 1 I have a separate table that the pdc_pt_prod_acs value is set to 1 when the heat pump is producing Domestic Hot Water. I need to use the following query as a filter.
SELECT mean(“value”) FROM “state” WHERE (“entity_id” = ‘pdc_pt_prod_acs’) AND $timeFilter GROUP BY time(5s) fill(previous) tz(‘Europe/Rome’)
How can achive this goal? Join ?!?
Thanks everyone!
The answer is pretty simple, you can’t do that using InfluxQL as it doesn’t support JOIN.
Your best bet is either:
-
Flux, if supported by your InfluxDB version (I’m not knowledgeable about it)
- Grafana transformations (docs here)
I’ve been able to kind of emulate a Join using the “Merge” transformations (note… the “outer join” transformation is not at all an outer join).
in your case, the merge key will be only the timestamp (so both queries must have the same aggregation interval for the points to match)
Here is my set of transformations, just to give you a reference, you probably won’t need the second one but the other will be handy
Hi, thanks for your reply! Are you italian?
An idea comes to mind. Can I add to the main table the “pdc_pt_prod_acs” column of the “state” table with the corresponding values based on time?
By doing so I don’t need to query two different tables and just put the filter on the main table
As there is no way of Joining different queries using InfluxQL (and you want to stick with it), having all the data in the same “table” is a solution.
the state “column” can be either a tag or a field, either way, you will be able to filter it in the WHERE clause (you can filter on field values, even if not indexed)
(and yes I’m Italian)
I’m italian too !
Thanks a lot for the tips! I have found that my grafana and my influxdb (docker on hassio) support Flux. I then added the datasource in Flux format instead of InfluxQL and I can get the data. Now I try to join the tables … (I’m from Turin)
ma domanda: se ti pagassi una consulenza mi aiuteresti a fare un paio di query in Flux?
Sadly I won’t be helpful as I don’t know Flux, or better I’ve studied it a bit when it was first available for InfluxDB1 and never actually used it. (and I have neither the time nor need to learn it now).
You will find plenty of topics on the forum about how to transition from InfluxQL to Flux, and if those are not enough you can post your own.
