Graph from independent data points

Setup, Configuration and Use Scripts & Rules
, ,
1

Hi,
I need to graph some datapoints to sitemap or grafana, but I have the data values in 48 separate items.
So I can’t use one persisted item to plot it over time.
I get today’s and tomorrows energy prices in-front and data is updated once per day.
What is the best way to do it?

items conf:

//Prices Tomorrow
String NP_Electricity_price_tomorrow_00_01 "Electricity price Tomorrow 00-01 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[0].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_01_02 "Electricity price Tomorrow 01-02 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[1].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_02_03 "Electricity price Tomorrow 02-03 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[2].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_03_04 "Electricity price Tomorrow 03-04 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[3].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_04_05 "Electricity price Tomorrow 04-05 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[4].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_05_06 "Electricity price Tomorrow 05-06 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[5].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_06_07 "Electricity price Tomorrow 06-07 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[6].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_07_08 "Electricity price Tomorrow 07-08 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[7].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_08_09 "Electricity price Tomorrow 08-09 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[8].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_09_10 "Electricity price Tomorrow 09-10 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[9].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_10_11 "Electricity price Tomorrow 10-11 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[10].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_11_12 "Electricity price Tomorrow 11-12 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[11].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_12_13 "Electricity price Tomorrow 12-13 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[12].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_13_14 "Electricity price Tomorrow 13-14 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[13].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_14_15 "Electricity price Tomorrow 14-15 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[14].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_15_16 "Electricity price Tomorrow 15-16 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[15].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_16_17 "Electricity price Tomorrow 16-17 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[16].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_17_18 "Electricity price Tomorrow 17-18 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[17].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_18_19 "Electricity price Tomorrow 18-19 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[18].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_19_20 "Electricity price Tomorrow 19-20 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[19].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_20_21 "Electricity price Tomorrow 20-21 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[20].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_21_22 "Electricity price Tomorrow 21-22 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[21].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_22_23 "Electricity price Tomorrow 22-23 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[22].Columns[0].Value)]" }
String NP_Electricity_price_tomorrow_23_00 "Electricity price Tomorrow 23-00 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[23].Columns[0].Value)]" }

//Prices Today
String NP_Electricity_price_today_00_01 "Electricity price Today 00-01 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[0].Columns[1].Value)]" }
String NP_Electricity_price_today_01_02 "Electricity price Today 01-02 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[1].Columns[1].Value)]" }
String NP_Electricity_price_today_02_03 "Electricity price Today 02-03 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[2].Columns[1].Value)]" }
String NP_Electricity_price_today_03_04 "Electricity price Today 03-04 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[3].Columns[1].Value)]" }
String NP_Electricity_price_today_04_05 "Electricity price Today 04-05 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[4].Columns[1].Value)]" }
String NP_Electricity_price_today_05_06 "Electricity price Today 05-06 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[5].Columns[1].Value)]" }
String NP_Electricity_price_today_06_07 "Electricity price Today 06-07 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[6].Columns[1].Value)]" }
String NP_Electricity_price_today_07_08 "Electricity price Today 07-08 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[7].Columns[1].Value)]" }
String NP_Electricity_price_today_08_09 "Electricity price Today 08-09 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[8].Columns[1].Value)]" }
String NP_Electricity_price_today_09_10 "Electricity price Today 09-10 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[9].Columns[1].Value)]" }
String NP_Electricity_price_today_10_11 "Electricity price Today 10-11 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[10].Columns[1].Value)]" }
String NP_Electricity_price_today_11_12 "Electricity price Today 11-12 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[11].Columns[1].Value)]" }
String NP_Electricity_price_today_12_13 "Electricity price Today 12-13 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[12].Columns[1].Value)]" }
String NP_Electricity_price_today_13_14 "Electricity price Today 13-14 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[13].Columns[1].Value)]" }
String NP_Electricity_price_today_14_15 "Electricity price Today 14-15 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[14].Columns[1].Value)]" }
String NP_Electricity_price_today_15_16 "Electricity price Today 15-16 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[15].Columns[1].Value)]" }
String NP_Electricity_price_today_16_17 "Electricity price Today 16-17 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[16].Columns[1].Value)]" }
String NP_Electricity_price_today_17_18 "Electricity price Today 17-18 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[17].Columns[1].Value)]" }
String NP_Electricity_price_today_18_19 "Electricity price Today 18-19 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[18].Columns[1].Value)]" }
String NP_Electricity_price_today_19_20 "Electricity price Today 19-20 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[19].Columns[1].Value)]" }
String NP_Electricity_price_today_20_21 "Electricity price Today 20-21 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[20].Columns[1].Value)]" }
String NP_Electricity_price_today_21_22 "Electricity price Today 21-22 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[21].Columns[1].Value)]" }
String NP_Electricity_price_today_22_23 "Electricity price Today 22-23 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[22].Columns[1].Value)]" }
String NP_Electricity_price_today_23_00 "Electricity price Today 23-00 [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[23].Columns[1].Value)]" }
2

The built-in charts are made for presenting the stored (persisted) values of an item over time. Using actual values on those charts is not supported.

3

Is there a way to manually combine those values to crate artificial series?
And then can the future range be displayed?

4

I would put those values in a database and use Grafana the present it and not the built-in charts (there is a group of users that do use Grafana in openHAB, search the forum for that). For putting the values in a database please refer to a forum of the chosen database.

5

No success in “putting values to database”.
I’m using influxdb + grafana. But I can’t find any forum posts that describe my case.
I have also tried to use REST API, but got stuck: https://community.openhab.org/t/rest-api-persistence-service-not-modifiable-influxdb/70710.
I can’t use simple item value update rule, because it would only persist data at given moment (now).
As my data is with future timestamps- I need to write data to database as value-time pairs.

6

You will not find any posts in openhab referencing your desire because openHAB does not support the use of databases in that way. However, directly using databases (without openhab) could solve your problem. In order to find help for that you should ask on forums referencing your selected database.

7

I tried to edit influxdb directly with curl but couldn’t figure it out (I’m new to the topic).
if I understand correctly then I should be able to do it through openHAB via REST API (persistance PUT), shouldn’t I?

image
image.png1379×332 28.6 KB

8

It MIGHT be possible to insert data points for a future time in a dadatabase that way (NOT for rrd4j!).
Displaying data for the future is NOT possible using standard openHAB charts!

9

I can set grafana timescale to future. Can’t test with real data though.

image
image.png1146×502 21.6 KB

10

That is what I said, not with standard openHAB charts but with Grafana.

11

OK, I intend to use grafana charts with habpanel

12

I’m now able to manually write data to influxdb:
https://community.openhab.org/t/rest-api-persistence-service-not-modifiable-influxdb/70710/16
https://community.openhab.org/t/solved-openhab-2-and-executecommandline-exec/21558/39

Next step would be to automate the whole process.
This rule works:

rule "Write NordPool energy prices to Influxdb"
		when 
			Item Renew_price changed
		then
			val results = executeCommandLine("curl@@-i@@-XPOST@@http://192.168.50.30:8086/write?db=openhab_db@@--data-binary@@NP_Electricity_price_test value=919 1435362189575692180@@",5000)
            logInfo("NPpriceDB.rules",results)
end

I need to cycle through all the value and time pairs:

executeCommandLine("curl@@-i@@-XPOST@@http://192.168.50.30:8086/write?db=openhab_db@@--data-binary@@NP_Electricity_price_test value=[price of the hour].state [fixed time of the day in Epoch format]@@",5000)

Do I understand correctly that “value=[price of the hour].state” part is simply value=NP_Electricity_price_today_00_01_num.state?

How is time conversion made ?
There is a nice example here:
https://community.openhab.org/t/datetime-conversion/54266

Number MyEpochFromDateTimeTypeItem_VariantA = (MyDateTimeTypeItem.state as DateTimeType).zonedDateTime.toInstant.toEpochMilli

But I can’t use dynamical time “now” as my “time brackets” are fixed.
Is there a way to do something like this: “today.plusminutes(0)”,“today.plusminutes(60)”,“today.plusminutes(120)” etc?

And lastly, what is the best practice for the amount of datapoints if I want a bar-graph type of chart not smooth curves, as my graph shows prices that are constant for the hour and then they change for the next hour. Do I need to give two equal value datapoints in the beginning of the hour and in the end of it or is one datapoint enough to show flat bar-graph?

13

Instead of “today” use “now.withTimeAtStartOfDay”

14

I have come up with this now:

rule "Write NordPool energy prices to Influxdb"
		when 
			Item Renew_price changed
		then
			val results = executeCommandLine("curl@@-i@@-XPOST@@http://192.168.50.30:8086/write?db=openhab_db@@--data-binary@@NP_Electricity_price_test value="+ NP_Electricity_price_today_00_01_num.state +" "+ (now.withTimeAtStartOfDay.plusHours(0).millis as DateTimeType).zonedDateTime.timeInMillis +"000000" ,5000)
            logInfo("NPpriceDB.rules",results)
end

log:

2019-03-24 19:53:54.876 [ERROR] [ntime.internal.engine.RuleEngineImpl] - Rule 'Write NordPool energy prices to Influxdb': Could not cast 1553378400000 to org.eclipse.smarthome.core.library.types.DateTimeType; line 5, column 203, length 60

log shows that it even calculates the epoch time. As I need nanoseconds epoch, I have added 000000 by force, but for some reason they don’t add up to this string.
Is there a way to have the epoch output in nanoseconds?

15

You can use a sendHttpPostRequest to push arbitrary data into InfluxDB. See this post. Curl will also work as you found. Also, I think there may be a .persist method for items? Not sure about that.

16

In the Display tab there are Mode Options - try enabling Staircase.

1 Like
17

I can’t use persist method as my input data is coming from separate items and I’m combining them into new single one.
I need to figure out how to convert my DateTime to epoch in nanosecond format. And then I assume I need to convert it into string as well in the end.
If I understood correctly then time format can be changed in influxdb configuration from RFC3339 to epoch and vice versa, but only one works at the time.
As persistence binding uses epoch already, I can’t change it without creating a chaos. Maybe I have misunderstood something.

18

Did you consider putting all the different Items in a group, then using a rule to update a single combined Item if any of them change? Then persist only that Item? Just a thought, I don’t know if it will work for you.

19

This is an interesting idea for some future items, haven’t thought of it this way!
Unfortunately my datapoints arrive all at once so I need to direct each of them to their right location in timeline.
My rule almost works already. I just need to figure out how to convert the time in this java monster- I lack skills. But it’s a learning curve. Some days ago I didn’t know what curl is :slight_smile:

20

I think it is solved now.
I had over-engineered and misunderstood the the timestamp part.
this one works:

rule "Write NordPool energy prices to Influxdb"
		when 
			Item Renew_price changed
		then
			val results = executeCommandLine("curl@@-i@@-XPOST@@http://192.168.50.30:8086/write?db=openhab_db@@--data-binary@@NP_Electricity_price_test value="+ NP_Electricity_price_today_00_01_num.state +" "+ now.withTimeAtStartOfDay.plusHours(2).millis ,5000)
            logInfo("NPpriceDB.rules",results)
end

old/wrong timestamp:

(now.withTimeAtStartOfDay.plusHours(0).millis as DateTimeType).zonedDateTime.timeInMillis +"000000"

new working timestamp:

now.withTimeAtStartOfDay.plusHours(2).millis

I had missed the fact that rules use Joda DateTime, not DateTime Type.
So converting it to Epoch was a lot easier.
Secondly, Influxdb was happy not to receive the time in nanoseconds.

Thanks to everyone for thinking along.

1 Like