Can't get InfluxDB data to Grafana when seetting my own timestamp

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!

1 Like

This was embarrising. I just multiplied the time with 1000000 and now it worked.

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*1000000
        //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

Hi @kosken
I’m about to start using Nordpool as well, and are considering different ways to do this.

Your solution gets SE3 if I’m not mistaken?
will you run this “rule Spotpriser” once every day at 14:30 or something?
how would you use it in rules (e.g. checking current/next hour prices and then act on it)

Hey,

Yes, i trigger the rule on Time cron “0 0 13,14 ? * * *”, so once at 13:00 and a second time at 14:00 CET. Yes, i’m in SE3.

I’m still trying to figure out how to set my heating based on the prices since I want different behaviour based on different price curves. So at the moment I just find the hour with the highest price and at that point in time I lower the heating. So very basic at the moment. If you come up with a more sophisticated solution let me know. At the moment my motivation is pretty low at the moment :smiley:

Prices are insane, and also cold… Not a good combo, so I fully get why your motivation is a bit low… :wink:

Why are you running it at 1300? Wouldn’t you get tomorrow at around 1400, and that’s it?

Are you assigning the result to some openhab variables/arrays, or are you querying the db whenever you want to find the highest price? If so, would you be ok to share some code around this (how to read the data back from db)? (I have not been using the http binding and the influx db much…)

Hi Martin.
I had almost simular idea. But mine is more simple… I´m searching for an idea/scripts, on how to poll for the actual hourly spotprice (DK2 on Nordpool), (Ie, I want to know what the price is right now, and store it in an item. Each hour it should pool Nordpool to update the price).

Have you got any idea how to handle that?

Sorry for late reply.

I run it at 13 because the prices are released at 12:45 and then I run it again at 14 just in case something goes wrong with the first run (prices are late, timeout error or similar)

Everything is still very basic so right now I’m finding the hour with the highest price and I store that in an item and then I do stuff (or rather don’t do stuff :smiley: ) at that hour. Not very smart at the moment

You should be able to build on the rule I’ve done. But you don’t loop it (skip the while clause) and extract the price for the current hour.

In the below code the variable row should be the current hour. I think it should be like this:
at 00:00 it should be 0
at 01:00 it should be 1
etc.

val spotPrice = transform("JSONPATH", "$.data.Rows[" + rows +"].Columns[2].Value",sendHttpGetRequest("https://www.nordpoolgroup.com/api/marketdata/page/29?currency=SEK"))
1 Like

And then of course you need to find the correct “page” for DK2

Paste the below URL in your browser and change the number 29 (up and down) until you get a page that states DK2.
https://www.nordpoolgroup.com/api/marketdata/page/29?currency=SEK

Did some digging and seems to be page 41
so use:
https://www.nordpoolgroup.com/api/marketdata/page/41?currency=DK
I also changed the currency to DK which I assume you prefer :smiley:

Hmm, Just tried the link i my broswer. I think its wrong… The data has both DK1 and DK2. And it also seems like it contains more than one hour… But maybe I have the extract the one hour (actualy hour) myself?

Check the post before this one. The URL does indeed return all the hours but with the JSON transform you can extract the price for a specific hour

Ahh I see… I´ll give it a try… Thx alot.

So for me, I am still learning about how my house behaves at different outside temperatures, switching different heating on/off.

I am leaning towards using a python library, e.g. GitHub - samuelmr/nordpool-node: A npm library to get information from nordic power market (Nord Pool, ELSPOT) or GitHub - Jalle19/nordpool-influxdb: A collection of scripts for storing Nordpool price data in InfluxDB to allow calculations and influxdb population etc to be done outside OH. It seems leaner, and probably easier to do/maintain. (Only exporting condensed information to OH for use in rules).

Currently I am thinking about a graded 1-4 score of prices, that is based on some average price (average of today/tomorrow, or maybe a few days back).
Score 1 & 2 will be below average, where 1 is “Really good price” (charge your car now etc), and 2 is “Good for being today, but not great” (needed for e.g. heating )
Within each score, all hours are sorted in a falling “goodness”, eg if hour 1,2,3,4,23 are all score 1, the cheapest will be the first.

But maybe there needs to be a sufficient distribution over a time period (so there is enough ‘1/2’ on each day to keep house warm) that depends on the outside temperature and probably forecast as well. So maybe actually it might be good to do this within OH. Hmm… :slight_smile:

If you guys find a good way to relate and use the spot prices, please share them. This is maybe another topic though…

Hi im trying to use this method to fetch data from Nordpool, it working sort of… data is being fetched but not formated as it should…i cant figure how to make the dotComma.js file i have tried various way to do this with no luck.

Does anybody have the dotComma.js

Thanks Mads

Hi Mads.

I think I’ve made some changes to the rule. The latest one is here:

rule "Spotpriser"
when
    Time cron "0 0 13,14,19 ? * * *"
then
    var rows = 0
    var i = 0
    var Number maxPrice = 0
    var Number priceDiff = 0
    var String maxPriceDate
    while((i=i+1) < 25) {
        //get the spot price
        val Number 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 (.), remove spaces and divide by 10
        val spotPriceDot = transform("JS", "spotPriceTrans.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*1000000
        //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)
        priceDiff = Double::parseDouble(spotPriceDot.toString) - maxPrice
        if(priceDiff > 0){
            maxPrice = Double::parseDouble(spotPriceDot.toString)
            maxPriceDate = startTime
        }
        rows = rows + 1
    }
    logInfo(" Spot", "Maxpriset kommer vara " + maxPrice + " och det kommer ske kl " + maxPriceDate)
    maxElPriceComing.postUpdate(maxPrice.toString)
    maxElPriceComingDate.postUpdate(maxPriceDate)
end

So I use the below JS transform now to handle some other things as well :slight_smile:

//Wrap everything in a function
(function(i) {
    //Removes spaces and declares the variable
    var spotPrice = i.replace(" ","");
    //Replace comma with dot
    spotPrice = spotPrice.replace(",",".")
    //divide the value by 10 to convert to öre/kWh and sets to 2 decimals, unless the values is 0
    if (spotPrice!==0){
        spotPrice = Number(spotPrice/10).toFixed(2);
    }
    return Number(spotPrice)
})(input)
// input variable contains data passed by openhab

Let me know if you need anything else

1 Like

Hi Martin, NICE, ill try to make work :slight_smile:

Thanks Mads

Hi

Its working when i use the swedish “https://www.nordpoolgroup.com/api/marketdata/page/29?currency=SEK
but when i tried the danish “https://www.nordpoolgroup.com/api/marketdata/page/41?currency=DK

i get this error

2022-04-08 06:26:11.427 [ERROR] [internal.handler.ScriptActionHandler] - Script execution of rule with UID 'Nordpool-2' failed: Character N is neither a decimal digit number, decimal point, nor "e" notation exponential mark. in Nordpool

Ok figured it :slight_smile:

This line needed to be changed to fit the Danish api page, there only 2 columns available.

 String spotPrice = transform("JSONPATH", "$.data.Rows[" + rows +"].Columns[0].Value",sendHttpGetRequest("https://www.nordpoolgroup.com/api/marketdata/page/41?currency=DK&endDate="))

This where the changed is needed

The Swedish api page has 4 columns (this is the NORD POOL DAY-AHEAD for Sweden)

Great that you figured it out!

Hi,

I just wanted to say thanks for the rule from @kosken and contribute with my first shot at something more sophisticated for controlling heating based on the prices.

I check tomorrow´s prices at 23:30 and create an array (or rather ArrayList) with three price levels based on some logic. Then I check the price level every hour and adjust heating and warm water production according to that.

import java.util.ArrayList;
import java.util.Collections;

var ArrayList<Integer> spotPriceLevel = new ArrayList<Integer>(Collections.nCopies(24, 2))

rule "Fetch Spotpriser"
when
    Time cron "0 30 23 ? * * *"
then
    logInfo("ElspotRule", "Hämtning av spotpriser startade")
    var rows = 0
    var i = 0
    var Number maxPrice = 0
    var Number priceDiff = 0
    var String maxPriceDate
    var ArrayList<Double> spotPrices = new ArrayList<Double>()

    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 (.), remove spaces and divide by 10
        val spotPriceDot = transform("JS", "spotPriceTrans.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*1000000
        // 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("ElspotRule", "The spot price is " + spotPriceDot + " and the timestamp is " + startTime + ". In Epoch " + startTimeEpoch)
        //logInfo("ElspotRule", "The command result " + input)
        
        priceDiff = Double::parseDouble(spotPriceDot.toString) - maxPrice
        if(priceDiff > 0){
            maxPrice = Double::parseDouble(spotPriceDot.toString)
            maxPriceDate = startTime
        }
        
        // Add price to arraylist
        spotPrices.add(Double::parseDouble(spotPriceDot.toString))
        rows = rows + 1
    }

	// Update arraylist with pricelevels, 1 to 3 where 3 is highest price
    var int j = 0
    
    for (double x : spotPrices) {
    	var double max = Collections.max(spotPrices)
        
        // Set price level to 3 for the eight most expensive hours or if price > 4.0 SEK/kWh
        if(j < 8 || max > 400.0 ) {
        	spotPriceLevel.set(spotPrices.indexOf(max), 3)
        }
        // Set price level to 1 for the three cheapest hours or if price is less than 0.6 SEK/kWh
        else if(j > 20 || max < 60.0) {
        	spotPriceLevel.set(spotPrices.indexOf(max), 1)
        }
        else {
        	spotPriceLevel.set(spotPrices.indexOf(max), 2)
        }
            
        spotPrices.set(spotPrices.indexOf(max), -1.0)
        
        j = j + 1
    }
    
    logInfo("ElspotRule", "spotPriceLevel " + spotPriceLevel.toString())

    logInfo("ElspotRule", "Maxpriset kommer vara " + maxPrice + " och det kommer att inträffa kl " + maxPriceDate)
   
end


rule "Check current elspot price level every hour"
when
    Time cron "0 0 * ? * * *"
then
    var currentHour = (LocalDateTime.now()).getHour()
    logInfo("ElspotRule", "Current hour " + currentHour.toString())
    logInfo("ElspotRule", "Current price level " + spotPriceLevel.get(currentHour).toString())
    try {
        switch(spotPriceLevel.get(currentHour)) {
        case 1:
            Current_Electricity_Price_Level.postUpdate("Low")
        case 2:
            Current_Electricity_Price_Level.postUpdate("Medium")
        case 3:
            Current_Electricity_Price_Level.postUpdate("High")
        default:
            Current_Electricity_Price_Level.postUpdate("Medium")
        }
    }
    catch(Throwable t) {
        logError("ElspotRule", "Error when setting price level " + t.toString())
        Current_Electricity_Price_Level.postUpdate("Medium")
    }

end

Cheers :slight_smile: