Postgres to Chart

Dashboard
1

Hi, I'm new to Node Red so please try to bare with me.

I'm running it on a raspberry pi 3 with a postgresql database on the same device. I have no issue pulling the data from the database but for some reason I can not get the data plotted. I've formatted the data as best as possible but still cant get anywhere. I've read a lot about the chart input format and think I have it correct but still not working. I've even done a simple random generator to chart input, put a debug on the output of the chart and tried to copy the exact same format but still cant get my chart to work.

I was originally working with several days worth of data, then thought it was too much so cut it down to just 10 points but still no joy. I've copied the payload from the database output and put it into this dummy flow to try and test the charting and to share a flow with you guys. I'm sure it's something very very simple going wrong but for the life of me can't figure it out. I've been working this for 2 days solid now so really would love some help.

[{"id":"3921690.72a5598","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"6755d009.c977","type":"ui_chart","z":"3921690.72a5598","name":"test data","group":"7a4ca97c.7b0a78","order":2,"width":0,"height":0,"label":"test data","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":793,"y":140,"wires":[[]]},{"id":"7f104c38.306144","type":"inject","z":"3921690.72a5598","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":189,"y":137,"wires":[["f86347c0.554c38"]]},{"id":"f86347c0.554c38","type":"function","z":"3921690.72a5598","name":"test data","func":"msg.payload = [{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-04T19:08:41.587Z\",\"value\":\"20.81\"},{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-04T19:04:09.702Z\",\"value\":\"20.63\"},{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-04T11:15:11.764Z\",\"value\":\"21.25\"},{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-04T11:14:11.766Z\",\"value\":\"21.31\"},{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-04T11:13:12.647Z\",\"value\":\"21.44\"},{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-04T11:12:11.755Z\",\"value\":\"21.31\"},{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-04T11:11:11.760Z\",\"value\":\"21.44\"},{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-04T11:10:11.766Z\",\"value\":\"21.38\"},{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-04T11:09:12.647Z\",\"value\":\"21.31\"},{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-04T11:08:11.777Z\",\"value\":\"21.19\"}];\nmsg.topic = \"CharliesBedroom\";\nreturn msg;","outputs":1,"noerr":0,"x":364,"y":141,"wires":[["4c921398.7625ac","42a4f62.b258608"]]},{"id":"4c921398.7625ac","type":"debug","z":"3921690.72a5598","name":"test data","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":570,"y":224,"wires":[]},{"id":"42a4f62.b258608","type":"change","z":"3921690.72a5598","name":"Format data","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t  $series := [\t    { \"field\": \"value\", \"label\": \"CharliesRoom\" }\t  ];\t  $xaxis := \"datetime\";\t  [\t    {\t      \"series\": [$series.label],\t      \"data\": $series.[\t        (\t          $yaxis := $.field;\t          $$.payload.{\t            \"x\": $toMillis($string(($lookup($, $xaxis)))),\t            \"y\": $number($lookup($, $yaxis))\t          }\t        )\t      ],\t      \"labels\": [\"\"]\t    }\t  ]\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":574.5,"y":137,"wires":[["de5f412d.f013c","6755d009.c977"]]},{"id":"de5f412d.f013c","type":"debug","z":"3921690.72a5598","name":"test data output","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":789,"y":225,"wires":[]},{"id":"7a4ca97c.7b0a78","type":"ui_group","name":"Group 1","tab":"b82fb60e.f8a9e8","order":1,"disp":true,"width":6},{"id":"b82fb60e.f8a9e8","type":"ui_tab","name":"Tab 1","icon":"dashboard","order":1}]
2

Time flows in wrong direction in your data array. Chart doesn't like it.

image
image336Ă—571 6.04 KB

If you really need to show time backwards, you'll need to manipulate chart options for x-axis via msg.ui_control but I think there is just small mistake you have made somewhere in data requesting.

3

Thank you very much for the feedback. I changed the order of the times but still no joy for some reason. Here's the updated flow with the corrected data.

[{"id":"6755d009.c977","type":"ui_chart","z":"3921690.72a5598","name":"test data","group":"7a4ca97c.7b0a78","order":2,"width":0,"height":0,"label":"test data","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":793,"y":140,"wires":[[]]},{"id":"7f104c38.306144","type":"inject","z":"3921690.72a5598","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":189,"y":137,"wires":[["f86347c0.554c38"]]},{"id":"f86347c0.554c38","type":"function","z":"3921690.72a5598","name":"test data","func":"msg.payload = [{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-01T20:44:38.276Z\",\"value\":\"20.25\"},{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-01T20:46:03.055Z\",\"value\":\"20.38\"},{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-01T20:46:42.408Z\",\"value\":\"20.38\"},{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-01T20:47:32.249Z\",\"value\":\"20.38\"},{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-01T20:48:32.248Z\",\"value\":\"20.50\"},{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-01T20:49:32.327Z\",\"value\":\"20.38\"},{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-01T20:50:32.407Z\",\"value\":\"20.25\"},{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-01T20:51:32.488Z\",\"value\":\"20.44\"},{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-01T20:52:32.487Z\",\"value\":\"20.44\"},{\"room\":\"CharliesBedroom\",\"datetime\":\"2019-12-01T20:53:32.568Z\",\"value\":\"20.44\"}];\nmsg.topic = \"CharliesBedroom\";\nreturn msg;","outputs":1,"noerr":0,"x":364,"y":141,"wires":[["4c921398.7625ac","42a4f62.b258608"]]},{"id":"4c921398.7625ac","type":"debug","z":"3921690.72a5598","name":"test data","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":570,"y":224,"wires":[]},{"id":"42a4f62.b258608","type":"change","z":"3921690.72a5598","name":"Format data","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t  $series := [\t    { \"field\": \"value\", \"label\": \"CharliesRoom\" }\t  ];\t  $xaxis := \"datetime\";\t  [\t    {\t      \"series\": [$series.label],\t      \"data\": $series.[\t        (\t          $yaxis := $.field;\t          $$.payload.{\t            \"x\": $toMillis($string(($lookup($, $xaxis)))),\t            \"y\": $number($lookup($, $yaxis))\t          }\t        )\t      ],\t      \"labels\": [\"\"]\t    }\t  ]\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":574.5,"y":137,"wires":[["de5f412d.f013c","6755d009.c977","7a03f7d7.680188"]]},{"id":"de5f412d.f013c","type":"debug","z":"3921690.72a5598","name":"test data output","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":789,"y":225,"wires":[]},{"id":"b012edc5.188de","type":"debug","z":"3921690.72a5598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":919.5,"y":371,"wires":[]},{"id":"7a03f7d7.680188","type":"function","z":"3921690.72a5598","name":"datatypes","func":"msg.xdatatype = typeof(msg.payload[0].data[0].x);\nmsg.ydatatype = typeof(msg.payload[0].data[0].y);\nreturn msg;","outputs":1,"noerr":0,"x":754.5,"y":299,"wires":[["b012edc5.188de"]]},{"id":"7a4ca97c.7b0a78","type":"ui_group","name":"Group 1","tab":"b82fb60e.f8a9e8","order":1,"disp":true,"width":6},{"id":"b82fb60e.f8a9e8","type":"ui_tab","name":"Tab 1","icon":"dashboard","order":1}]
4

There is one missing array in data formatting.
Data should be array of series. (where series is array of objects)
Correct parsing will be

($series := [
    { "field": "value", "label": "CharliesRoom" }
  ];
  $xaxis := "datetime";
  [
    {
      "series": [$series.label],
      "data": $series.[[
        (
          $yaxis := $.field;
          $$.payload.{
            "x": $toMillis($string(($lookup($, $xaxis)))),
            "y": $number($lookup($, $yaxis))
          }
        )
      ]],
      "labels": [""]
    }
])
5

Your an absolute start, I would never of seen this.

Thank you so much.

Just a note to anyone else reading this post with the same issue. Please note that my parsing in the actual flow with an actual real postgres query is ever so slightly different due to the fact that the postgres node returns the datetime as an object rather than a string. To get an object converted toMills you first need to convert it to a sting but in doing so, it also copies the speech marks on either side of the string to a string so the result is a string inside double speech marks with the inner speech marks escaped. To correct this I had to convert the object to a string, substring the converted string to remove the first and last characters (in this case speech marks) then convert that result toMills.

 (
  $series := [
    { "field": "value", "label": "CharliesRoom" }
  ];
  $xaxis := "datetime";
  [
    {
      "series": [$series.label],
      "data": $series.[[
        (
          $yaxis := $.field;
          $$.payload.{
            "x": $toMillis($substring($string(($lookup($, $xaxis))),1,22)),
            "y": $number($lookup($, $yaxis))
          }
        )
      ]],
      "labels": [""]
    }
  ]
)
1 Like