I have a super simple time series with one value per day of the solar production.
I seems I can’t query for the values from the current month only and from the last finished month. Grafana has such a filters in the graph display but I can’t figure out the syntax for that…
Can anyone help me with the syntax on that?
select * from B_Power_energy_daily_log
B_Power_energy_daily_log
time value
2017-01-18T21:27:30.293Z 134.513
2017-01-19T21:06:00.045Z 5.951
2017-01-20T21:06:00.04Z 12.539
2017-01-21T22:50:00.115Z 13.033
2017-01-22T22:50:00.028Z 11.301
2017-01-23T21:20:00.027Z 13.73
2017-01-24T21:22:00.05Z 8.992
That would be really limiting!
But I see that Grafana can take months (like “1M”) in the Time Range - Override relative time field.
Does it mean it is localy calculated in the client and passed into influx as a date range?
Workaround mode on. Would that to be possible to get the date range prepared in the OH2 with the rule and then somehow insert it into the influx/grafana query with the newly supported subquery option?
Or can a rule in OH2 make a custom query to the persistence DB? and put that value into the varaible?
You want openHAB to query for “mean() over a certain time range” and store that inside an openHAB number item?
Totally possible, but not with the influxdb persistence service. You will have to build your own script, which could run once per day and update an openHAB item via REST API. Check out the forum for examples.
A script to access an influxdb instance is easy. Either use the HTTP interface or one of the programming APIs
I need sum(“values”) of the daily solar production values from the last month.
I will have to resort to shell/pyton script that will run on crontab that will query influx from a CLI and publish that with MQTT to populate the variable in OH. For some reason use MQTT for all external script communication
Just to to be able to populate that one field in the Grafana dashboard.
Meh, lots of work
Python script that does call a shell script to execute influx query with correct time boundaries for the last month. Influx CLI seems to only take date in the epoch format. I cron it for 1 minute past midnight on the 1st of each month.
And a companion shell script get_influx_data.sh as I was to lazy to escape the whole thing in Python.
#!/bin/sh
influx -username 'admin' -password 'SuperSecretPassword123+' -database 'openhab_db' -precision=rfc3339 -execute "select sum(value) from B_Power_energy_daily_log WHERE time >= $1 AND time < $2"