Hi,
I’m polling electricity prices for the coming day from an API. I store these prices in my InfluxDB, but since it’s in the future I need to add my own timestamp and therefore I use the InfluxDB API. Finally I use Grafana to draw graphs. However, for some reason Grafana is not able to retrieve any data.
I have one raspberry pi 3b running Openhab 3.1 (latest stable release) and influxDB and another raspberry pi 3b running Grafana, both running Raspbian Buster. I have other Items that are stored in InfluxDB through the persistence service which I’m able to draw graphs with so things are working.
This is my rule where I get the data and via Curl command insert it into InfluxDB.
rule "Spotpriser"
when
Item testSwitch received update
then
var rows = 0
var i = 0
while((i=i+1) < 25) {
//get the spot price
val spotPrice = transform("JSONPATH", "$.data.Rows[" + rows +"].Columns[2].Value",sendHttpGetRequest("https://www.nordpoolgroup.com/api/marketdata/page/29?currency=SEK"))
//get the start time for the spot price
val String startTime = transform("JSONPATH", "$.data.Rows[" + rows +"].StartTime",sendHttpGetRequest("https://www.nordpoolgroup.com/api/marketdata/page/29?currency=SEK"))
//replace the comma (,) with dot (.) before sending to influx
val spotPriceDot = transform("JS", "dotComma.js", spotPrice)
//transform the timestamp (string) to Java LocalDateTime then add timezone to get ZonedDateTime and finally to Epoch millis
val startTimeEpoch = ((LocalDateTime.parse(startTime)).atZone(ZoneId.systemDefault())).toInstant.toEpochMilli
//write the value to influxDB
val input = executeCommandLine(Duration.ofSeconds(5), "curl", "-i", "-XPOST", "http://localhost:8086/write?db=openhab_db", "--data-binary", "NordPoolSpotPrice,item=NordPoolSpotPrice value=" + spotPriceDot + " "+ startTimeEpoch)
logInfo(" Spot", "The spot price is " + spotPriceDot + " and the timestamp is " + startTime + ". In Epoch " + startTimeEpoch)
logInfo("Spot price", "The command result " + input)
rows = rows + 1
}
end
When I check in InfluxDB things seems ok when I compare with my other items except for the timestamp. It seems like my other items are stored in Unix time in nano seconds and the prices with milliseconds.
Here is an item stored through persistence:
> select * from HusdataH60_VarmvattenTemperatur where value > 50
name: HusdataH60_VarmvattenTemperatur
time item value
---- ---- -----
1636323705228000000 HusdataH60_VarmvattenTemperatur 50.3
1636323719362000000 HusdataH60_VarmvattenTemperatur 50.5
1636323775901000000 HusdataH60_VarmvattenTemperatur 50.3
1636323803937000000 HusdataH60_VarmvattenTemperatur 50.5
1636323832335000000 HusdataH60_VarmvattenTemperatur 50.3
1636323874745000000 HusdataH60_VarmvattenTemperatur 50.5
Here is the electricity price item
> select * from NordPoolSpotPrice
name: NordPoolSpotPrice
time item value
---- ---- -----
1636930800000 NordPoolSpotPrice 142.42
1636934400000 NordPoolSpotPrice 140.12
1636938000000 NordPoolSpotPrice 136.81
1636941600000 NordPoolSpotPrice 132.5
1636945200000 NordPoolSpotPrice 136.01
1636948800000 NordPoolSpotPrice 901.09
1636952400000 NordPoolSpotPrice 983.11
1636966800000 NordPoolSpotPrice 997.83
1636970400000 NordPoolSpotPrice 986.22
In Grafana I add the electricity price in the same way that I do with the other items but I get no data.
Here are two queries. One for the electricity price and the other one for the item I showed above.
SELECT mean("value") FROM "NordPoolSpotPrice" WHERE time >= now() - 6h GROUP BY time(1m) fill(null);SELECT mean("value") FROM "HusdataH60_VarmvattenTemperatur" WHERE time >= now() - 6h GROUP BY time(1m) fill(previous)
When I insert a value with the same Curl except for the timestamp (I leave it blank) I get data in Grafana. What should I do to get a valid timestamp for Grafana to read? Or can I change some setting in Grafana to also accept Milli seconds?
Thanks!