Hello,
I’m pretty new to influxDB and to SQL. So i’m not sure it’s possible what i’m trying to accomplish.
I’ve setup a datapush from my Domoticz server to influxDB to create graphs in Grafana. This al works and I love the way it all looks.
But, Domoticz sends the total gas usage instead of the current use. What I want to do is show a Singlestat with the usage of this day. What I came up with is. I get the last value of yesterday and subtract that from the last value of today. That will give me todays usage.
So to test this I have 2 querys (doing the above but then with 4 hours apart):
- 10 minutes ago:
curl -i -XPOST ‘http:/0.0.0.0:0000/query?pretty=true’ --data-urlencode “db=domoticz” --data-urlencode “q=SELECT last("value") AS sum_lastNow FROM "Gas-usage" WHERE time >= now()-10m LIMIT 1”
Result:
“name”: “Gas-usage”,
“columns”: [
“time”,
“sum_lastNow”
],
“values”: [
[
“2019-01-23T14:15:48.764348885Z”,
114.633
]
]
4 hours ago:
curl -i -XPOST ‘http:/0.0.0.0:0000/query?pretty=true’ --data-urlencode “db=domoticz” --data-urlencode “q=SELECT first("value") AS sum_lastYes FROM "Gas-usage" WHERE time >= now()-4h LIMIT 1”
Result:
“name”: “Gas-usage”,
“columns”: [
“time”,
“sum_lastYes”
],
“values”: [
[
“2019-01-23T10:23:11.87750021Z”,
114.153
]
]
So that works. But now i want to combine the 2 and subtract them:
curl -i -XPOST ‘http:/0.0.0.0:0000/query?pretty=true’ --data-urlencode “db=domoticz” --data-urlencode “q=SELECT "sum_lastNow" - "sum_lastYes" AS result FROM (SELECT last("value") AS sum_lastYes FROM "Gas-usage" WHERE time >= now()-4h LIMIT 1), (SELECT last("value") AS sum_lastNow FROM "Gas-usage" WHERE time >= now() -10m LIMIT 1)”
So this results:
“name”: “Gas-usage”,
“columns”: [
“time”,
“result”
],
“values”: [
[
“2019-01-23T14:22:38.550934512Z”,
null
],
[
“2019-01-23T14:22:38.550934512Z”,
null
]
]
Maybe this isn’t even possible or i’m just doing it stupidly.
Any advise would be welcome.
(i’ve changed the ip’s and ports and also the http part, got an error i can only post 2 links)
Greetings,
Jos
