From sqlite to json format

NODE777 · 25 February 2020 04:40

I'm trying to read data from sqlite db previously storage with sqlite node then process that info to chart it in node red.
I been read the following info but I don't quite understand what type of node red file I would need to use to retrieve such a data.

Stored data
To display a complete chart in one go - for example from a set of points retrieved from a database, the data must be supplied in the form of an array, that holds an object that has series,labels, and data arrays. This is broadly the same as the raw format used by the angular chart.js library.

You will need to process your data into this structure in order to render it correctly.

msg.payload = [{

"series": ["A"],*
"data": [msg.payload],*
"labels": [""]*
}];
return msg;

I did not found which is the the data format sqlite node storage the info.
If Would know that, maybe I would use node-red-contrib-json-db or other like file out node
The tests I done does't make sense I got garbage.
Can I get some tips for that?

Thank you in advance.

dceejay · 25 February 2020 08:23

you will probably need a function node and to write some javascript code to convert it from one format to the other. But without us knowing the format of the data you are getting from the sqlite node it's hard to guess further than that.

Colin · 25 February 2020 10:06

Start by feeding the output of the sqlite node into a debug node to see what format the data is.

NODE777 · 25 February 2020 16:30

The attached pic shows the output of a sqlite node through the debug node.
So far, those are column headers I am currently using and my idea is to chart certain values as a time function just showing hh:mm. on X-axis.

2/25/2020, 8:14:27 AMnode: 1bfef614.c985ea
SELECT * FROM hvac4 : msg.payload : array[424]
array[424]
[0 … 9]
[10 … 19]
[20 … 29]
[30 … 39]
30: object
id: 31
UTC: 1582525461104
DateTime: "2020/02/23T22:24:21"
hp: 67.7
lp: 73.8
cltmp: null
evaptmp: null
cmpI: null
device: null
31: object
32: object
33: object
34: object
35: object
id: 36
UTC: 1582526961138
DateTime: "2020/02/23T22:49:21"
hp: 67.2
lp: 73.3
cltmp: null

Thank you

spyder0552 · 25 February 2020 21:06

Update:
Try using J expression via the change node.
I use the change node to take the payload, convert to Jsonata and get what I need.

so yours would be something like
[
{
"Series": "HVACSomething",
"values:" msg.payload.[DateTime,hp]
}
]

Note that you need to tell it which columns from your payload (since it is an array of values) you want to plot. It will look for date fields and put them on the Y axis for you.

Been there and still fighting with this myself. Here is what I do to trouble shoot this.

add the chart you want.
hook an inject node to and set the payload of the inject to the sample data from the chart. This will give you a chart...just not with your data.
Now hook a debug node up after the inject so you can see exactly what the chart is looking for.

Example for what I am doing:
[
{
"key": "HistAmps",
"values": msg.payload.[EntryDate, Amps]
}
]

I had to format it exactly they way it wanted. Also, notice that after the msg.payload I have a .[X,Y]. this is to tell the system which columns from that array I want to use.

again, the trick to the charts is to see what the sample data looks like and then make your data look the same.

NODE777 · 26 February 2020 04:01

@spyder0552 Thank you, I'll give a try to it
It's very interesting your suggestion.

NODE777 · 27 February 2020 05:22

@spyder0552

Hi

I tried your suggestion, I got some payload but really my background with Jsonata is not strong and really I'm a little be lost.
This is my partial flow to get some stored value in the db, based on selection of the start day and the end day .

I followed you suggestion :
[
{
"Series": "HVACSomething",
"values:" msg.payload.[DateTime,hp]
}
]

At the change node output I got this:

2/26/2020, 8:58:35 PMnode: HP
SELECT DateTime, hp FROM hvac4 WHERE UTC BETWEEN 1582347482759 AND 1582520194611 : msg : Object
object
payload: array[1]
0: object
series: "HVACHP"
values: array[13]
[0 | 9]
0: array[2]
0: "2020/02/23T20:28:20"
1: 74.5
1: array[2]
2: array[2]
3: array[2]
4: array[2]
5: array[2]
6: array[2]
7: array[2]
8: array[2]
9: array[2]
[10 | 12]
socketid: "0ayuh1naNtxuMH6lAAAA"
_msgid: "61aeeb34.b7a524"
topic: " SELECT DateTime, hp FROM hvac4 WHERE UTC BETWEEN 1582347482759 AND 1582520194611 "

As you can see each of those arrays include two elements:
0: "2020/02/23T20:28:20"
1: 74.5

If I inject that payload in the chart it give me this at the debug tab:

2/26/2020, 9:14:54 PMnode: c316face.a34ed8
msg : string[15]
"Bad data inject"

So I believe that the payload to inject in chart is not correctly processed.

Can you help me with that?
Thank you in advance.

zenofmud · 27 February 2020 11:05

So you are going to plot the entire set of rows returned. First off, the date/time fieild is a text string (sqlite does not have a date format) that you need to change back to a javascript date (google 'w3schools date').
Secondly you need to build the array of data to be sent - see the Stored Data section of this writeup: https://github.com/node-red/node-red-dashboard/blob/master/Charts.md

NODE777 · 28 February 2020 18:47

Hi thank you for your suggestion.
I get what you said regarding the time, I'm using UTC time in ms now.
I sow the examples on the link you sent me but a question remind.
I sow the different data format but I do not have idea how to processes my data
to get those format for the chart.
I would need to pass from this:

I took this from the last post I sent.
values: array[13]
[0 | 9]
0: array[2]
0: 15346758697979
1: 74.5
1: array[2]
2: array[2]

To something like this:

"data":[
          [{"UTC":1504029632890,"hp":50},{"UTC":1504029636001,"hp":420},{"UTC":1504029638656,"hp":102}]
         ,[{"UTC":1504029633514,"hp":160},{"UTC":1504029636622,"hp":78},{"UTC":1504029639539,"hp":126}],
          [{"UTC":1504029634400,"hp":70},{"UTC":1504029637959,"hp":99},{"UTC":1504029640317,"hp":127}]
       ],

Would you help me with that?

zenofmud · 28 February 2020 21:50

Be glad to help you. So what have you tried? Can you show how you are building the payload to match what it shows in the link I pointed you to?

NODE777 · 1 March 2020 19:32

Well, I did partially my assignment
I reached the point where a have the data structure to inject to chart but I got stuck trying to convert to JSON format the chart "y" values .

One remark is that I have no intention to chart so many point in time when I 'm using the pick date node to select between dates.
The db has stored more than necessary values over the time just test db node operation.
Having said that, the following is the flow I'm working on.

The function node called " get chart format" has the following code:

var series = msg.payload.series
var values = msg.payload.values;
var labels=  msg.payload.labels
var chart =[{
    
  "series":  [series],
   "data" :  [values],
  "labels" : [labels ]
       
   } ]
   
msg.payload= chart;

return msg;

The "get chart format" function output is:

3/1/2020, 10:56:21 AMnode: Get Chart format output
SELECT UTC , hp FROM hvac4 WHERE UTC BETWEEN 1582483181618 AND 1582742381618 : msg : Object
object
payload: array[1]
0: object
series: array[1]
0: "HVACHP"
data: array[1]
0: array[735]
[0 … 9]
[10 … 19]
[20 … 29]
20: array[2]
0: 1582522461043
1: 67.7
21: array[2]
22: array[2]
23: array[2]
24: array[2]
25: array[2]
26: array[2]
27: array[2]
28: array[2]
29: array[2]
[30 … 39]
[40 … 49]
[50 … 59]
[60 … 69]
[70 … 79]
[80 … 89]
[90 … 99]
[100 … 109]
[110 … 119]
[120 … 129]
[130 … 139]
[140 … 149]
[150 … 159]
[160 … 169]
[170 … 179]
[180 … 189]
[190 … 199]
[200 … 209]
[210 … 219]
[220 … 229]
[230 … 239]
[240 … 249]
[250 … 259]
[260 … 269]
[270 … 279]
[280 … 289]
[290 … 299]
[300 … 309]
[310 … 319]
[320 … 329]
[330 … 339]
[340 … 349]
[350 … 359]
[360 … 369]
[370 … 379]
[380 … 389]
[390 … 399]
[400 … 409]
[410 … 419]
[420 … 429]
[430 … 439]
[440 … 449]
[450 … 459]
[460 … 469]
[470 … 479]
[480 … 489]
[490 … 499]
[500 … 509]
[510 … 519]
[520 … 529]
[530 … 539]
[540 … 549]
[550 … 559]
[560 … 569]
[570 … 579]
[580 … 589]
[590 … 599]
[600 … 609]
[610 … 619]
[620 … 629]
[630 … 639]
[640 … 649]
[650 … 659]
[660 … 669]
[670 … 679]
[680 … 689]
[690 … 699]
[700 … 709]
[710 … 719]
[720 … 729]
[730 … 734]
labels: array[1]
0: array[1]
0: ""
socketid: "Nu8OOYbVn9URsKeJAAAB"
topic: " SELECT UTC , hp FROM hvac4 WHERE UTC BETWEEN 1582483181618 AND 1582742381618 "
parts: object
id: "e9ce528b.e5ccd"
type: "array"
count: 1
len: 1
index: 0
_msgid: "8e3179e0.0cc3e8"

So now I get stuck how to convert the property in JSON format,
I tried this with the node JSON Stringnify withou succes.
This is the code:

var payload =JSON.stringify(msg.payload.data);

msg.payload= payload;
return msg;

data: array[1]
0: array[735]
20: array[2]
0: 1582522461043
1: 67.7
I would need your help to giving me some tip to convert the above array
at the following format at least "data" property

[{
"series": ["A", "B", "C"],
"data": [
    [{ "x": 1504029632890, "y": 45 },
     { "x": 1504029636001, "y": 90 },
     { "x": 1504029638656, "y": 89 }
    ],
    [{ "x": 1504029633514, "y": 100},
     { "x": 1504029636622, "y": 200 },
     { "x": 1504029639539, "y": 60 }
    ],
    [{ "x": 1504029634400, "y": 76 },
     { "x": 1504029637959, "y": 79 },
     { "x": 1504029640317, "y": 89 }
    ]
],
"labels": [""]
}]

Thank you in advance.

zenofmud · 1 March 2020 21:58

Please read this then export your flow and attach it to a reply.

NODE777 · 2 March 2020 01:19

Thanks for the answer.

This is the code corresponding to the flow I already posted before.

[{"id":"7dfd9399.1514dc","type":"tab","label":"Flow 8","disabled":false,"info":""},{"id":"e790367.f6ec3c8","type":"ui_date_picker","z":"7dfd9399.1514dc","name":"Start date2","label":"stday2","group":"e5436490.b53788","order":1,"width":"0","height":"0","passthru":false,"topic":"","x":330,"y":260,"wires":[["5e4eef1d.d924"]]},{"id":"deac3d5a.727c8","type":"ui_date_picker","z":"7dfd9399.1514dc","name":"End date2","label":"enday2","group":"e5436490.b53788","order":2,"width":"0","height":"0","passthru":false,"topic":"","x":330,"y":320,"wires":[["dac6eab4.6e2a38"]]},{"id":"5e4eef1d.d924","type":"moment","z":"7dfd9399.1514dc","name":"stday","topic":"","input":"payload","inputType":"msg","inTz":"America/Los_Angeles","adjAmount":"0","adjType":"hours","adjDir":"subtract","format":"x","locale":"en_US","output":"stdayhp","outputType":"global","outTz":"America/Los_Angeles","x":490,"y":260,"wires":[["20b1d764.57ffd8"]]},{"id":"dac6eab4.6e2a38","type":"moment","z":"7dfd9399.1514dc","name":"enday","topic":"","input":"payload","inputType":"msg","inTz":"America/Los_Angeles","adjAmount":"0","adjType":"hours","adjDir":"subtract","format":"x","locale":"en_US","output":"endayhp","outputType":"global","outTz":"America/Los_Angeles","x":490,"y":320,"wires":[["20b1d764.57ffd8"]]},{"id":"4bcb2d9f.3b0954","type":"comment","z":"7dfd9399.1514dc","name":"select between dates","info":"ToString(\"yyyy-MM-dd\");\n\nSELECT * FROM mytalbe WHERE date BETWEEN '2014-10-09 00:00:00' AND '2014-10-10 23:59:59'","x":380,"y":200,"wires":[]},{"id":"20b1d764.57ffd8","type":"function","z":"7dfd9399.1514dc","name":"SELECT hp FROM hvac4 WHERE UTC BETWEEN stday AND enday","func":"var stday2=global.get('stdayhp') || 0;\nvar enday2=global.get('endayhp') || 0; \n\nvar b= \" SELECT UTC , hp FROM hvac4 WHERE UTC BETWEEN \"+ stday2 +\" AND \"+ enday2+\" \" \n\nmsg.topic= b;\n\n\nreturn msg;\n","outputs":1,"noerr":0,"x":850,"y":260,"wires":[["e3d55511.7b5f58"]]},{"id":"e3d55511.7b5f58","type":"sqlite","z":"7dfd9399.1514dc","mydb":"43f5a38e.29f69c","sqlquery":"msg.topic","sql":"","name":"Sqlite","x":1190,"y":260,"wires":[["d605ef73.1ca27"]]},{"id":"3871b5f6.f23a5a","type":"ui_table","z":"7dfd9399.1514dc","group":"cbf3450e.fde788","name":"HP betwen dates","order":1,"width":8,"height":"6","columns":[],"outputs":0,"cts":false,"x":1570,"y":260,"wires":[]},{"id":"570d3919.fc5b08","type":"ui_chart","z":"7dfd9399.1514dc","name":"","group":"871e15d4.6b09d8","order":0,"width":0,"height":0,"label":"chart","chartType":"line","legend":"false","xformat":"auto","interpolate":"linear","nodata":"","dot":true,"ymin":"0","ymax":"600","removeOlder":"0","removeOlderPoints":"1000","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":1550,"y":400,"wires":[[]]},{"id":"d605ef73.1ca27","type":"json","z":"7dfd9399.1514dc","name":"","property":"payload","action":"obj","pretty":false,"x":1330,"y":260,"wires":[["3871b5f6.f23a5a","5c5c7dbb.4d7a44"]]},{"id":"5c5c7dbb.4d7a44","type":"change","z":"7dfd9399.1514dc","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"[ { \"series\" : \"HVACHP\", \"values\" : msg.payload . [ UTC , hp ]\t , \"labels\" : [\"\"] }\t\t]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":680,"y":420,"wires":[["24e4341e.db2b2c"]]},{"id":"77b85bfc.4b5fb4","type":"debug","z":"7dfd9399.1514dc","name":"HP","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1530,"y":340,"wires":[]},{"id":"24e4341e.db2b2c","type":"split","z":"7dfd9399.1514dc","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":870,"y":420,"wires":[["b48b5bcf.f9afb8"]]},{"id":"b48b5bcf.f9afb8","type":"function","z":"7dfd9399.1514dc","name":"get char format","func":"\n\nvar series = msg.payload.series\nvar values = msg.payload.values;\nvar labels= msg.payload.labels\nvar chart =[{\n \n \"series\": [series],\n \"data\" : [values],\n \"labels\" : [labels ]\n \n \n } ]\n \nmsg.payload= chart;\n\n\n\nreturn msg;","outputs":1,"noerr":0,"x":1040,"y":420,"wires":[["b531ef6b.b4ef1","b40fa449.0cf758"]]},{"id":"b40fa449.0cf758","type":"debug","z":"7dfd9399.1514dc","name":"Get Chart format output","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1310,"y":480,"wires":[]},{"id":"b531ef6b.b4ef1","type":"function","z":"7dfd9399.1514dc","name":"JSON.stringify","func":"var payload =JSON.stringify(msg.payload.data);\n\nmsg.payload= payload;\nreturn msg;","outputs":1,"noerr":0,"x":1300,"y":380,"wires":[["570d3919.fc5b08","77b85bfc.4b5fb4"]]},{"id":"e5436490.b53788","type":"ui_group","z":"","name":"HP betwwen dates","tab":"324b1683.2accda","order":2,"disp":true,"width":"6","collapse":true},{"id":"43f5a38e.29f69c","type":"sqlitedb","z":"","db":"C:\\Users\\amvel\\hvac4.db","mode":"RWC"},{"id":"cbf3450e.fde788","type":"ui_group","z":"","name":"HP","tab":"aef35186.22a0c","order":1,"disp":true,"width":"8","collapse":true},{"id":"871e15d4.6b09d8","type":"ui_group","z":"","name":"Selection","tab":"aef35186.22a0c","order":1,"disp":true,"width":"5","collapse":false},{"id":"324b1683.2accda","type":"ui_tab","z":"","name":"Reports","icon":"dashboard","order":9,"disabled":false,"hidden":false},{"id":"aef35186.22a0c","type":"ui_tab","z":"","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

This is the db snapshot

:

Thank you.

zenofmud · 2 March 2020 11:01

I see one issue right away. As soon as you pick eithor date, a msg will go thru the moment nodes and into the function node and the function node will build the SQL statement based on only one date and what ever the previous global date was.

so you should:

disconnect the moment nodes from the function node
add a button connected to the function node that you press after selecting the two dates.
add logic to make sure the end date is greater than the start date.

Also can you copy the data from the database snapshot and paste it to a reply so I could recreate that data without having to type all the data in (which I will NOT do)

NODE777 · 3 March 2020 04:42

Hi

Please see attached modified flow as you required it:

[{"id":"7dfd9399.1514dc","type":"tab","label":"Flow 8","disabled":false,"info":""},{"id":"e129f2f9.10385","type":"ui_date_picker","z":"7dfd9399.1514dc","name":"Start date2","label":"stday2","group":"e5436490.b53788","order":1,"width":"0","height":"0","passthru":false,"topic":"","x":70,"y":700,"wires":[["bd0e5b4c.6f5a68"]]},{"id":"6a2a886c.daa4a8","type":"ui_date_picker","z":"7dfd9399.1514dc","name":"End date2","label":"enday2","group":"e5436490.b53788","order":2,"width":"0","height":"0","passthru":false,"topic":"","x":90,"y":780,"wires":[["ef35d616.4e8e18"]]},{"id":"2080fb57.e707b4","type":"function","z":"7dfd9399.1514dc","name":"SELECT hp FROM hvac4 WHERE UTC BETWEEN stday AND enday","func":"var stday2=global.get('stdayhp') || 0;\nvar enday2=global.get('endayhp') || 0; \n\nvar b= \" SELECT UTC , hp FROM hvac4 WHERE UTC BETWEEN \"+ stday2 +\" AND \"+ enday2+\" \" \n\nmsg.topic= b;\n\n\nreturn msg;\n","outputs":1,"noerr":0,"x":1110,"y":720,"wires":[["e40b735d.5e3f"]]},{"id":"e40b735d.5e3f","type":"sqlite","z":"7dfd9399.1514dc","mydb":"43f5a38e.29f69c","sqlquery":"msg.topic","sql":"","name":"Sqlite","x":1450,"y":720,"wires":[["576c467c.20c188"]]},{"id":"ad94f8d2.909048","type":"ui_table","z":"7dfd9399.1514dc","group":"cbf3450e.fde788","name":"HP betwen dates","order":1,"width":8,"height":"6","columns":[],"outputs":0,"cts":false,"x":1830,"y":720,"wires":[]},{"id":"c7449d1d.ef204","type":"ui_chart","z":"7dfd9399.1514dc","name":"","group":"871e15d4.6b09d8","order":0,"width":0,"height":0,"label":"chart","chartType":"line","legend":"false","xformat":"auto","interpolate":"linear","nodata":"","dot":true,"ymin":"0","ymax":"100","removeOlder":"0","removeOlderPoints":"1000","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":1770,"y":860,"wires":[[]]},{"id":"576c467c.20c188","type":"json","z":"7dfd9399.1514dc","name":"","property":"payload","action":"obj","pretty":false,"x":1590,"y":720,"wires":[["ad94f8d2.909048","e214e6e4.96d618","14039bfe.3821f4"]]},{"id":"e214e6e4.96d618","type":"change","z":"7dfd9399.1514dc","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"[ { \"series\" : \"HVACHP\", \"values\" : msg.payload . [ UTC , hp ]\t , \"labels\" : [\"\"] }\t\t]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":940,"y":880,"wires":[["1ecbc358.47a6cd"]]},{"id":"1ecbc358.47a6cd","type":"split","z":"7dfd9399.1514dc","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":1130,"y":880,"wires":[["23c419bf.12c0a6"]]},{"id":"23c419bf.12c0a6","type":"function","z":"7dfd9399.1514dc","name":"get char format","func":"\n\nvar series = msg.payload.series\nvar values = msg.payload.values;\nvar labels= msg.payload.labels\nvar chart =[{\n \n \"series\": [series],\n \"data\" : [values],\n \"labels\" : [labels ]\n \n \n } ]\n \nmsg.payload= chart;\n\n\n\nreturn msg;","outputs":1,"noerr":0,"x":1300,"y":880,"wires":[["c961e5e9.12e128","cac20be.225d7f8"]]},{"id":"c961e5e9.12e128","type":"debug","z":"7dfd9399.1514dc","name":"Get Chart format output","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1570,"y":940,"wires":[]},{"id":"14039bfe.3821f4","type":"debug","z":"7dfd9399.1514dc","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1810,"y":660,"wires":[]},{"id":"a3b278fd.fd1e38","type":"debug","z":"7dfd9399.1514dc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":630,"y":820,"wires":[]},{"id":"1006f150.5e8c7f","type":"ui_button","z":"7dfd9399.1514dc","name":"","group":"40ef9319.95bd7c","order":2,"width":0,"height":0,"passthru":true,"label":"Enter selected range","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"date","topic":"","x":720,"y":720,"wires":[["2080fb57.e707b4"]]},{"id":"f42db48f.3e9c08","type":"function","z":"7dfd9399.1514dc","name":"Enday > Starday","func":"\n\n var stday2=global.get('stdayhp') || 0;\n var enday2=global.get('endayhp') || 0; \n\n\nif ( enday2 >stday2 ) {\n \n \n return msg;\n}\n\nelse {\n stday2=0 ;\n enday2=0; \n\n}\n\n\n\n","outputs":1,"noerr":0,"x":450,"y":740,"wires":[["1006f150.5e8c7f","a3b278fd.fd1e38"]]},{"id":"ef35d616.4e8e18","type":"moment","z":"7dfd9399.1514dc","name":"enday","topic":"","input":"payload","inputType":"msg","inTz":"America/Los_Angeles","adjAmount":"0","adjType":"hours","adjDir":"subtract","format":"x","locale":"en_US","output":"endayhp","outputType":"global","outTz":"America/Los_Angeles","x":250,"y":780,"wires":[["f42db48f.3e9c08"]]},{"id":"bd0e5b4c.6f5a68","type":"moment","z":"7dfd9399.1514dc","name":"stday","topic":"","input":"payload","inputType":"msg","inTz":"America/Los_Angeles","adjAmount":"0","adjType":"hours","adjDir":"subtract","format":"x","locale":"en_US","output":"stdayhp","outputType":"global","outTz":"America/Los_Angeles","x":250,"y":700,"wires":[["f42db48f.3e9c08"]]},{"id":"cac20be.225d7f8","type":"function","z":"7dfd9399.1514dc","name":"JSON.stringify","func":"var payload =JSON.stringify(msg.payload.data.array);\n\nmsg.payload= payload;\nreturn msg;","outputs":1,"noerr":0,"x":1540,"y":860,"wires":[["c7449d1d.ef204"]]},{"id":"e5436490.b53788","type":"ui_group","z":"","name":"HP betwwen dates","tab":"324b1683.2accda","order":2,"disp":true,"width":"6","collapse":true},{"id":"43f5a38e.29f69c","type":"sqlitedb","z":"","db":"C:\\Users\\amvel\\hvac4.db","mode":"RWC"},{"id":"cbf3450e.fde788","type":"ui_group","z":"","name":"HP","tab":"aef35186.22a0c","order":1,"disp":true,"width":"8","collapse":true},{"id":"871e15d4.6b09d8","type":"ui_group","z":"","name":"Selection","tab":"aef35186.22a0c","order":1,"disp":true,"width":"5","collapse":false},{"id":"40ef9319.95bd7c","type":"ui_group","z":"","name":"Default","tab":"324b1683.2accda","disp":true,"width":"6","collapse":false},{"id":"324b1683.2accda","type":"ui_tab","z":"","name":"Reports","icon":"dashboard","order":9,"disabled":false,"hidden":false},{"id":"aef35186.22a0c","type":"ui_tab","z":"","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

This the db :

ID UTC DateTime ( local) hp lp

|1695|1583209921955|2020/03/02T20:32:01|77.1|74.2|
|---|---|---|---|---|
|1694|1583207096121|2020/03/02T19:44:56|78.2|76.6|
|1693|1583206453163|2020/03/02T19:34:13|77.9|76.4|
|1692|1583164232433|2020/03/02T07:50:32|74|78.1|
|1691|1583163932418|2020/03/02T07:45:32|73.3|75.2|
|1690|1583163632415|2020/03/02T07:40:32|73.1|73.7|
|1689|1583163332419|2020/03/02T07:35:32|73.1|72|
|1688|1583163032423|2020/03/02T07:30:32|73|72.1|
|1687|1583162732413|2020/03/02T07:25:32|73.4|72.7|
|1686|1583162432401|2020/03/02T07:20:32|73.5|73.3|
|1685|1583162132391|2020/03/02T07:15:32|72.6|71|
|1684|1583161832396|2020/03/02T07:10:32|72.7|71.2|
|1683|1583161532392|2020/03/02T07:05:32|72.9|71.8|
|1682|1583161232394|2020/03/02T07:00:32|72.9|72.2|
|1681|1583160932396|2020/03/02T06:55:32|72.9|72|
|1680|1583160632397|2020/03/02T06:50:32|72.9|71.6|
|1679|1583160332382|2020/03/02T06:45:32|73.3|72.1|
|1678|1583160032392|2020/03/02T06:40:32|73.2|71.9|
|1677|1583159732387|2020/03/02T06:35:32|72.6|70.2|
|1676|1583159432365|2020/03/02T06:30:32|72.7|70.3|
|1675|1583159132367|2020/03/02T06:25:32|72.8|70.4|
|1674|1583158832363|2020/03/02T06:20:32|73|70.9|
|1673|1583158532364|2020/03/02T06:15:32|73.4|71.7|
|1672|1583158232344|2020/03/02T06:10:32|73.7|72.5|
|1671|1583157932355|2020/03/02T06:05:32|72.8|70.2|
|1670|1583157632345|2020/03/02T06:00:32|72.9|70.1|
|1669|1583157332343|2020/03/02T05:55:32|73|70.2|
|1668|1583157032339|2020/03/02T05:50:32|73.1|70.6|
|1667|1583156732330|2020/03/02T05:45:32|73.3|71.1|
|1666|1583156432324|2020/03/02T05:40:32|73.8|72|
|1665|1583156132316|2020/03/02T05:35:32|73.8|72.4|
|1664|1583155832318|2020/03/02T05:30:32|73|70.3|
|1663|1583155532312|2020/03/02T05:25:32|73.1|70.3|
|1662|1583155232313|2020/03/02T05:20:32|73.2|70.4|
|1661|1583154932299|2020/03/02T05:15:32|73.3|70.6|
|1660|1583154632285|2020/03/02T05:10:32|73.5|70.7|
|1659|1583154332280|2020/03/02T05:05:32|73.6|71|
|1658|1583154032283|2020/03/02T05:00:32|73.8|71.3|
|1657|1583153732260|2020/03/02T04:55:32|73.9|71.8|
|1656|1583153432270|2020/03/02T04:50:32|74.4|72.8|
|1655|1583153132263|2020/03/02T04:45:32|74.3|73|
|1654|1583152832256|2020/03/02T04:40:32|73.7|70.9|
|1653|1583152532242|2020/03/02T04:35:32|73.7|71|
|1652|1583152232216|2020/03/02T04:30:32|73.9|71.1|
|1651|1583151932222|2020/03/02T04:25:32|74|71.3|
|1650|1583151632212|2020/03/02T04:20:32|74.1|71.8|
|1649|1583151332207|2020/03/02T04:15:32|74.5|72.4|
|1648|1583151032195|2020/03/02T04:10:32|75.1|73.8|
|1647|1583150732190|2020/03/02T04:05:32|74.2|72|
|1646|1583150432176|2020/03/02T04:00:32|74.3|71.5|
|1645|1583150132178|2020/03/02T03:55:32|74.4|71.8|
|1644|1583149832157|2020/03/02T03:50:32|74.7|72.5|
|1643|1583149532159|2020/03/02T03:45:32|75.4|73.9|
|1642|1583149232150|2020/03/02T03:40:32|75.4|74.6|
|1641|1583148932143|2020/03/02T03:35:32|75.4|74.6|
|1640|1583148632132|2020/03/02T03:30:32|75.4|74.6|
|1639|1583148332102|2020/03/02T03:25:32|75.4|74.6|
|1638|1583148032082|2020/03/02T03:20:32|74.8|72.6|
|1637|1583147732067|2020/03/02T03:15:32|75.2|73.3|
|1636|1583147432086|2020/03/02T03:10:32|75.6|75.2|
|1635|1583147132075|2020/03/02T03:05:32|75.1|73.8|
|1634|1583146832073|2020/03/02T03:00:32|75.2|73.4|
|1633|1583146532056|2020/03/02T02:55:32|75.8|75.4|
|1632|1583146232052|2020/03/02T02:50:32|75.3|74.5|
|1631|1583145932045|2020/03/02T02:45:32|75.2|74.2|
|1630|1583145632048|2020/03/02T02:40:32|74.7|75.2|
|1629|1583145332038|2020/03/02T02:35:32|75.4|74.7|
|1628|1583145032033|2020/03/02T02:30:32|76.2|76.5|
|1627|1583144732022|2020/03/02T02:25:32|76.1|76.4|
|1626|1583144432019|2020/03/02T02:20:32|75.5|75.2|
|1625|1583144132032|2020/03/02T02:15:32|75|73.7|
|1624|1583143832018|2020/03/02T02:10:32|75.8|76|
|1623|1583143532003|2020/03/02T02:05:32|75.5|75.5|
|1622|1583143232004|2020/03/02T02:00:32|74.8|73.8|
|1621|1583142931994|2020/03/02T01:55:31|74.9|73.6|
|1620|1583142631986|2020/03/02T01:50:31|75.5|75.7|
|1619|1583142331979|2020/03/02T01:45:31|74.9|74.4|
|1618|1583142031971|2020/03/02T01:40:31|74.6|72.9|
|1617|1583141731952|2020/03/02T01:35:31|75.2|75|
|1616|1583141431946|2020/03/02T01:30:31|74.8|74.3|
|1615|1583141131948|2020/03/02T01:25:31|74.2|72.3|
|1614|1583140831928|2020/03/02T01:20:31|74.7|73.3|
|1613|1583140531930|2020/03/02T01:15:31|74.8|74.4|
|1612|1583140231918|2020/03/02T01:10:31|74.1|72.6|
|1611|1583139931923|2020/03/02T01:05:31|74.2|72.2|
|1610|1583139631928|2020/03/02T01:00:31|74.7|74.2|
|1609|1583139331918|2020/03/02T00:55:31|74.2|73.1|
|1608|1583139031911|2020/03/02T00:50:31|73.6|71|
|1607|1583138731906|2020/03/02T00:45:31|73.8|71.9|
|1606|1583138431886|2020/03/02T00:40:31|74.4|73.6|
|1605|1583138131886|2020/03/02T00:35:31|73.6|71.7|
|1604|1583137831893|2020/03/02T00:30:31|73.3|70.1|
|1603|1583137531890|2020/03/02T00:25:31|73.4|70.3|
|1602|1583137231882|2020/03/02T00:20:31|73.5|70.9|
|1601|1583136931880|2020/03/02T00:15:31|74|71.9|
|1600|1583136631862|2020/03/02T00:10:31|73.6|70.7|
|1599|1583136331855|2020/03/02T00:05:31|73.4|69.8|
|1598|1583136031850|2020/03/02T00:00:31|73.4|70|
|1597|1583135731848|2020/03/01T23:55:31|73.8|70.4|
|1596|1583135431835|2020/03/01T23:50:31|73.8|70.4|
|1595|1583135131818|2020/03/01T23:45:31|73.8|70.4|
|1594|1583134831796|2020/03/01T23:40:31|73.9|70.4|
|1593|1583134531807|2020/03/01T23:35:31|73.9|70.5|
|1592|1583134231795|2020/03/01T23:30:31|74|70.5|
|1591|1583133931790|2020/03/01T23:25:31|74.1|70.7|
|1590|1583133631790|2020/03/01T23:20:31|74.2|70.8|
|1589|1583133331785|2020/03/01T23:15:31|74.3|71|
|1588|1583133031777|2020/03/01T23:10:31|74.4|71.1|
|1587|1583132731775|2020/03/01T23:05:31|74.4|71.1|
|1586|1583132431773|2020/03/01T23:00:31|74.5|71.1|
|1585|1583132131760|2020/03/01T22:55:31|74.6|71.2|
|1584|1583131831760|2020/03/01T22:50:31|74.6|71.2|
|1583|1583131531758|2020/03/01T22:45:31|74.7|71.3|
|1582|1583131231761|2020/03/01T22:40:31|74.9|71.1|
|1581|1583130931756|2020/03/01T22:35:31|74.5|71.1|
|1580|1583130631749|2020/03/01T22:30:31|75|71.5|
|1579|1583130331737|2020/03/01T22:25:31|75|71.6|
|1578|1583130031731|2020/03/01T22:20:31|76.3|73.1|
|1577|1583129731706|2020/03/01T22:15:31|76.3|73.1|
|1576|1583129431719|2020/03/01T22:10:31|76.3|73.1|
|1575|1583129131710|2020/03/01T22:05:31|76.3|73.1|
|1574|1583128831699|2020/03/01T22:00:31|76.3|73.1|
|1573|1583128531696|2020/03/01T21:55:31|76.3|73.1|
|1572|1583128231697|2020/03/01T21:50:31|76.3|73.1|
|1571|1583127931690|2020/03/01T21:45:31|76.3|73.1|
|1570|1583127631693|2020/03/01T21:40:31|76.3|73.1|
|1569|1583127331565|2020/03/01T21:35:31|76.3|73.1|
|1568|1583127031557|2020/03/01T21:30:31|76.3|73.1|
|1567|1583126731553|2020/03/01T21:25:31|76.3|73.1|
|1566|1583126431541|2020/03/01T21:20:31|76.3|73.1|
|1565|1583126131535|2020/03/01T21:15:31|76.3|73.1|
|1564|1583125831528|2020/03/01T21:10:31|76.3|73.1|
|1563|1583125531522|2020/03/01T21:05:31|76.3|73.1|
|1562|1583125231501|2020/03/01T21:00:31|76.4|73.3|
|1561|1583124931510|2020/03/01T20:55:31|76.4|73.3|
|1560|1583124631498|2020/03/01T20:50:31|76.4|73.3|
|1559|1583124331498|2020/03/01T20:45:31|76.5|73.4|
|1558|1583124031488|2020/03/01T20:40:31|76.6|73.5|
|1557|1583123731484|2020/03/01T20:35:31|76.6|73.6|
|1556|1583123431483|2020/03/01T20:30:31|76.8|73.7|
|1555|1583123131468|2020/03/01T20:25:31|76.9|73.8|
|1554|1583122831450|2020/03/01T20:20:31|77.3|74.5|
|1553|1583122531459|2020/03/01T20:15:31|77.3|74.5|
|1552|1583122231430|2020/03/01T20:10:31|77.3|74.5|
|1551|1583121931425|2020/03/01T20:05:31|77.3|74.5|
|1550|1583121631412|2020/03/01T20:00:31|77.3|74.5|
|1549|1583121331405|2020/03/01T19:55:31|77.3|74.5|
|1548|1583121031392|2020/03/01T19:50:31|77.3|74.5|
|1547|1583120731388|2020/03/01T19:45:31|78.1|76.2|
|1546|1583120431367|2020/03/01T19:40:31|78.1|76.2|
|1545|1583120131361|2020/03/01T19:35:31|78.1|76.2|
|1544|1583119831358|2020/03/01T19:30:31|78.1|76.2|
|1543|1583119531344|2020/03/01T19:25:31|76.8|74.8|
|1542|1583119231353|2020/03/01T19:20:31|76.8|74.8|
|1541|1583118931352|2020/03/01T19:15:31|76.7|74.3|
|1540|1583118631350|2020/03/01T19:10:31|76.5|74.8|
|1539|1583118331386|2020/03/01T19:05:31|76.5|74.9|
|1538|1583118031346|2020/03/01T19:00:31|76.6|74.9|
|1537|1583117731340|2020/03/01T18:55:31|76.6|74.5|
|1536|1583117431340|2020/03/01T18:50:31|76.6|74.9|
|1535|1583117131332|2020/03/01T18:45:31|76.7|75.1|
|1534|1583116831320|2020/03/01T18:40:31|76.7|75.2|
|1533|1583116531314|2020/03/01T18:35:31|76.8|75.3|
|1532|1583116231317|2020/03/01T18:30:31|76.8|75.3|
|1531|1583115931313|2020/03/01T18:25:31|76.8|75.3|
|1530|1583115631304|2020/03/01T18:20:31|76.8|75.4|
|1529|1583115331296|2020/03/01T18:15:31|76.9|75.4|
|1528|1583115031283|2020/03/01T18:10:31|76.9|75.4|
|1527|1583114731277|2020/03/01T18:05:31|77|75.4|
|1526|1583114431263|2020/03/01T18:00:31|77.1|75.5|
|1525|1583114131265|2020/03/01T17:55:31|77.2|75.6|
|1524|1583113831257|2020/03/01T17:50:31|77.3|75.7|
|1523|1583113531253|2020/03/01T17:45:31|77.3|75.7|
|1522|1583113231233|2020/03/01T17:40:31|77.4|75.7|
|1521|1583112931242|2020/03/01T17:35:31|77.4|75.9|
|1520|1583112631225|2020/03/01T17:30:31|77.5|76.1|
|1519|1583112331220|2020/03/01T17:25:31|77.5|76.4|
|1518|1583111902598|2020/03/01T17:18:22|77.6|76.6|
|1517|1583111540914|2020/03/01T17:12:20|77.5|76.9|
|1516|1583111240914|2020/03/01T17:07:20|77.8|77.3|
|1515|1583110940913|2020/03/01T17:02:20|77.8|77.4|
|1514|1583110640913|2020/03/01T16:57:20|77.7|77.5|
|1513|1583110340911|2020/03/01T16:52:20|77.6|77.6|
|1512|1583110040910|2020/03/01T16:47:20|77.9|78.3|
|1511|1583109740910|2020/03/01T16:42:20|77.9|78.4|
|1510|1583109440911|2020/03/01T16:37:20|77.9|78.6|
|1509|1583109140911|2020/03/01T16:32:20|77.9|78.8|
|1508|1583108840911|2020/03/01T16:27:20|77.8|79.2|
|1507|1583108540912|2020/03/01T16:22:20|77.4|79.3|
|1506|1583108240905|2020/03/01T16:17:20|78|80.1|
|1505|1583107940904|2020/03/01T16:12:20|78|80|
|1504|1583107640892|2020/03/01T16:07:20|77.9|79.3|
|1503|1583107340888|2020/03/01T16:02:20|77.9|79.2|
|1502|1583107040889|2020/03/01T15:57:20|77.9|79.1|
|1501|1583106740889|2020/03/01T15:52:20|77.9|78.9|
|1500|1583106440890|2020/03/01T15:47:20|77.9|78.6|
|1499|1583106140887|2020/03/01T15:42:20|77.9|78.8|
|1498|1583105840886|2020/03/01T15:37:20|77.9|79.4|
|1497|1583105540881|2020/03/01T15:32:20|77.9|80.3|
|1496|1583105240882|2020/03/01T15:27:20|77.9|80.4|
|1495|1583104940882|2020/03/01T15:22:20|77.9|80.5|
|1494|1583104640883|2020/03/01T15:17:20|77.8|80.5|
|1493|1583104340884|2020/03/01T15:12:20|77.9|80.5|
|1492|1583104040880|2020/03/01T15:07:20|77.8|80.7|
|1491|1583103740880|2020/03/01T15:02:20|77.6|80.7|
|1490|1583103440880|2020/03/01T14:57:20|77.4|81|
|1489|1583103140880|2020/03/01T14:52:20|77.4|81.1|
|1488|1583102840877|2020/03/01T14:47:20|76.7|81.3|
|1487|1583102540877|2020/03/01T14:42:20|76.8|81.8|
|1486|1583102240880|2020/03/01T14:37:20|77.4|82.5|
|1485|1583101940880|2020/03/01T14:32:20|77.4|83.2|
|1484|1583101640877|2020/03/01T14:27:20|77.3|85.3|
|1483|1583101340839|2020/03/01T14:22:20|77.3|85.3|
|1482|1583101040839|2020/03/01T14:17:20|77.3|85.3|
|1481|1583100740838|2020/03/01T14:12:20|77|88.8|
|1480|1583100440837|2020/03/01T14:07:20|77|88.8|
|1479|1583100140836|2020/03/01T14:02:20|77|88.8|
|1478|1583099840833|2020/03/01T13:57:20|77|88.8|
|1477|1583099540833|2020/03/01T13:52:20|77|88.8|
|1476|1583099240830|2020/03/01T13:47:20|77|88.8|
|1475|1583098940830|2020/03/01T13:42:20|77|91.2|
|1474|1583098640830|2020/03/01T13:37:20|76.3|104.3|
|1473|1583098340829|2020/03/01T13:32:20|76.3|104.3|
|1472|1583098040829|2020/03/01T13:27:20|76.3|104.3|
|1471|1583097740830|2020/03/01T13:22:20|76.3|104.3|
|1470|1583097440828|2020/03/01T13:17:20|76.3|104.3|
|1469|1583097140826|2020/03/01T13:12:20|76.3|104.3|
|1468|1583096840826|2020/03/01T13:07:20|76.3|104.3|
|1467|1583096540826|2020/03/01T13:02:20|76.2|104.3|
|1466|1583096240821|2020/03/01T12:57:20|76.2|104.3|
|1465|1583095940820|2020/03/01T12:52:20|76.2|102.7|
|1464|1583095640817|2020/03/01T12:47:20|76.3|102.7|
|1463|1583095133987|2020/03/01T12:38:53|76.4|105.7|
|1462|1583094833986|2020/03/01T12:33:53|76.4|106.2|
|1461|1583094533986|2020/03/01T12:28:53|77.4|108|
|1460|1583094233986|2020/03/01T12:23:53|77.3|108.1|
|1459|1583093933985|2020/03/01T12:18:53|77.3|108.4|
|1458|1583093633983|2020/03/01T12:13:53|77.3|108.5|
|1457|1583093333983|2020/03/01T12:08:54|77.2|108.6|
|1456|1583093031977|2020/03/01T12:03:51|77.2|108.9|
|1455|1583092731978|2020/03/01T11:58:51|77.2|109.3|
|1454|1583092431977|2020/03/01T11:53:51|77.1|109.6|
|1453|1583092131976|2020/03/01T11:48:51|77.1|109.8|
|1452|1583091831975|2020/03/01T11:43:51|77.1|109.7|
|1451|1583091531974|2020/03/01T11:38:51|76.9|109|
|1450|1583091231975|2020/03/01T11:33:51|77|109.9|
|1449|1583090931975|2020/03/01T11:28:51|76.9|110.3|
|1448|1583090631975|2020/03/01T11:23:51|76.9|110.2|
|1447|1583090331974|2020/03/01T11:18:51|76.8|110.2|
|1446|1583090031972|2020/03/01T11:13:51|76.7|111|
|1445|1583089731970|2020/03/01T11:08:51|76.6|111.1|
|1444|1583089431966|2020/03/01T11:03:51|76.5|110.4|
|1443|1583088484244|2020/03/01T10:48:04|76.3|97.5|
|1442|1583087955230|2020/03/01T10:39:15|76.2|93.6|
|1441|1583085114529|2020/03/01T09:51:54|78|77.7|
|1440|1583042276829|2020/02/29T21:57:56|77.6|77.3|
|1439|1583041976825|2020/02/29T21:52:56|77.5|77.1|
|1438|1583041464442|2020/02/29T21:44:24|77.2|77|
|1437|1583040749944|2020/02/29T21:32:29|76.7|76.4|
|1436|1583039699917|2020/02/29T21:14:59|74.5|72.1|
|1435|1583039399917|2020/02/29T21:09:59|74.9|72.2|
|1434|1583039099918|2020/02/29T21:04:59|74.9|72.2|
|1433|1583038799916|2020/02/29T20:59:59|75|72.2|
|1432|1583038499916|2020/02/29T20:54:59|75|72|
|1431|1583038199916|2020/02/29T20:49:59|75.1|72.2|
|1430|1583037899916|2020/02/29T20:44:59|75.3|72.5|
|1429|1583037599914|2020/02/29T20:39:59|75.3|72.5|
|1428|1583037299914|2020/02/29T20:34:59|75.4|72.5|
|1427|1583036999912|2020/02/29T20:29:59|75.5|72.7|
|1426|1583036699914|2020/02/29T20:24:59|75.6|72.7|
|1425|1583036399914|2020/02/29T20:19:59|75.8|72.9|
|1424|1583036099912|2020/02/29T20:14:59|75.9|73.3|
|1423|1583035799910|2020/02/29T20:09:59|76.1|73.7|
|1422|1583035499910|2020/02/29T20:04:59|76.6|74.5|
|1421|1583035199906|2020/02/29T19:59:59|78|76.3|
|1420|1583033741966|2020/02/29T19:35:41|76.7|76.2|
|1419|1583032911781|2020/02/29T19:21:51|76.2|75.8|
|1418|1583004085648|2020/02/29T11:21:25|74.1|88.6|
|1417|1583003785647|2020/02/29T11:16:25|74.1|90.5|
|1416|1583003485647|2020/02/29T11:11:25|74.1|92|
|1415|1583003185645|2020/02/29T11:06:25|74.1|94.1|
|1414|1583002885646|2020/02/29T11:01:25|74|99.4|
|1413|1583002585641|2020/02/29T10:56:25|74|100|
|1412|1583002285639|2020/02/29T10:51:25|73.9|103.9|
|1411|1583001985640|2020/02/29T10:46:25|73.9|106.5|
|1410|1583001685639|2020/02/29T10:41:25|73.8|101|
|1409|1583001385639|2020/02/29T10:36:25|73.7|104.4|
|1408|1583001085639|2020/02/29T10:31:25|73.7|101.2|
|1407|1583000785641|2020/02/29T10:26:25|73.7|93.1|
|1406|1583000485639|2020/02/29T10:21:25|73.7|87.5|
|1405|1583000185639|2020/02/29T10:16:25|73.7|87.4|
|1404|1582999885640|2020/02/29T10:11:25|73.6|84.3|
|1403|1582999585640|2020/02/29T10:06:25|73.5|83.9|
|1402|1582999285641|2020/02/29T10:01:25|73.6|82.8|
|1401|1582998985639|2020/02/29T09:56:25|73.6|81.4|
|1400|1582998685642|2020/02/29T09:51:25|73.6|80.4|
|1399|1582998385595|2020/02/29T09:46:25|73.6|79.9|
|1398|1582998085597|2020/02/29T09:41:25|73.7|79.8|
|1397|1582997785594|2020/02/29T09:36:25|73.5|79|
|1396|1582997485590|2020/02/29T09:31:25|73.4|78.6|
|1395|1582997185589|2020/02/29T09:26:25|73.5|78.2|
|1394|1582996885586|2020/02/29T09:21:25|73.6|78.1|
|1393|1582952708427|2020/02/28T21:05:08|77.5|74.5|
|1392|1582950998053|2020/02/28T20:36:38|77.9|75.1|
|1391|1582950412153|2020/02/28T20:26:52|78|75.4|
|1390|1582950112151|2020/02/28T20:21:52|78.2|75.6|
|1389|1582949812149|2020/02/28T20:16:52|78.3|75.9|
|1388|1582946696715|2020/02/28T19:24:56|78.6|78|
|1387|1582946396715|2020/02/28T19:19:56|78.5|77.9|
|1386|1582945603927|2020/02/28T19:06:43|78.7|77.3|
|1385|1582945303927|2020/02/28T19:01:43|77.9|77.3|
|1384|1582944689810|2020/02/28T18:51:29|77|76.2|
|1383|1582944389809|2020/02/28T18:46:29|76.4|74.6|
|1382|1582944089810|2020/02/28T18:41:29|76|73.4|
|1381|1582943789810|2020/02/28T18:36:29|76.1|73.5|
|1380|1582943489811|2020/02/28T18:31:29|76.1|73.6|
|1379|1582943189810|2020/02/28T18:26:29|76.1|73.7|
|1378|1582942889809|2020/02/28T18:21:29|76.1|73.7|
|1377|1582942589807|2020/02/28T18:16:29|76.1|73.8|
|1376|1582942289808|2020/02/28T18:11:29|76.1|73.9|
|1375|1582941989808|2020/02/28T18:06:29|76.1|73.9|
|1374|1582941689805|2020/02/28T18:01:29|76.2|74|
|1373|1582941389806|2020/02/28T17:56:29|76.2|74.1|
|1372|1582941089805|2020/02/28T17:51:29|76.2|74.1|
|1371|1582940789793|2020/02/28T17:46:29|76.2|74.3|
|1370|1582940489794|2020/02/28T17:41:29|76.2|74.4|
|1369|1582940189793|2020/02/28T17:36:29|76.2|74.5|
|1368|1582939889794|2020/02/28T17:31:29|76.2|74.7|
|1367|1582939589794|2020/02/28T17:26:29|76.2|74.9|
|1366|1582939289793|2020/02/28T17:21:29|76.2|75.1|
|1365|1582938989794|2020/02/28T17:16:29|76.2|75.3|
|1364|1582938689746|2020/02/28T17:11:29|76.3|75.6|
|1363|1582938389746|2020/02/28T17:06:29|76.3|75.9|
|1362|1582938089743|2020/02/28T17:01:29|76.2|76.3|
|1361|1582937789742|2020/02/28T16:56:29|76.2|76.5|
|1360|1582937489738|2020/02/28T16:51:29|76.3|76.9|
|1359|1582937189737|2020/02/28T16:46:29|76.4|77.2|
|1358|1582935743755|2020/02/28T16:22:23|76|78.2|
|1357|1582935443753|2020/02/28T16:17:23|76|78.4|
|1356|1582935143751|2020/02/28T16:12:23|76|78.4|
|1355|1582934843751|2020/02/28T16:07:23|75.5|78.3|
|1354|1582934543751|2020/02/28T16:02:23|75.2|79|
|1353|1582934243752|2020/02/28T15:57:23|75.3|79.4|
|1352|1582933943753|2020/02/28T15:52:23|75.2|80|
|1351|1582933643750|2020/02/28T15:47:23|75.3|80.7|
|1350|1582933343747|2020/02/28T15:42:23|75.2|81.2|
|1349|1582933043735|2020/02/28T15:37:23|75.3|81.4|
|1348|1582932743736|2020/02/28T15:32:23|75.4|81.4|
|1347|1582932443735|2020/02/28T15:27:23|75.4|81.2|
|1346|1582932143732|2020/02/28T15:22:23|75.4|81|
|1345|1582931843732|2020/02/28T15:17:23|75.4|80.8|
|1344|1582931543723|2020/02/28T15:12:23|75.4|80.5|
|1343|1582931243730|2020/02/28T15:07:23|75.4|80.6|
|1342|1582930943724|2020/02/28T15:02:23|75.4|81|
|1341|1582930643725|2020/02/28T14:57:23|75.4|81.5|
|1340|1582930343724|2020/02/28T14:52:23|75.4|81.9|
|1339|1582930043709|2020/02/28T14:47:23|75.3|81.7|
|1338|1582929743722|2020/02/28T14:42:23|75.3|81.5|
|1337|1582929443723|2020/02/28T14:37:23|75.3|81.4|
|1336|1582929143718|2020/02/28T14:32:23|75.2|81.6|
|1335|1582928843718|2020/02/28T14:27:23|75.3|81.9|
|1334|1582928543718|2020/02/28T14:22:23|75.2|82.8|
|1333|1582928243718|2020/02/28T14:17:23|75.2|83.6|
|1332|1582927943718|2020/02/28T14:12:23|75.3|83.8|
|1331|1582927643718|2020/02/28T14:07:23|75.3|84|
|1330|1582927343718|2020/02/28T14:02:23|75.4|84.1|
|1329|1582927043718|2020/02/28T13:57:23|75.4|84.1|
|1328|1582926743719|2020/02/28T13:52:23|75.7|84.1|
|1327|1582926443717|2020/02/28T13:47:23|75.7|84.1|
|1326|1582926143715|2020/02/28T13:42:23|75.7|83.6|
|1325|1582925843714|2020/02/28T13:37:23|75.7|83.6|
|1324|1582925543713|2020/02/28T13:32:23|75.7|84.1|
|1323|1582925243714|2020/02/28T13:27:23|75.7|84.5|
|1322|1582924943717|2020/02/28T13:22:23|75.7|84.2|
|1321|1582924643706|2020/02/28T13:17:23|75.6|83.9|
|1320|1582924343707|2020/02/28T13:12:23|75.6|84.1|
|1319|1582924043705|2020/02/28T13:07:23|75.6|84.2|
|1318|1582923743707|2020/02/28T13:02:23|75.6|85|
|1317|1582923443706|2020/02/28T12:57:23|75.6|86.4|
|1316|1582923143705|2020/02/28T12:52:23|75.6|86.9|
|1315|1582922843705|2020/02/28T12:47:23|75.6|86.1|
|1314|1582922543693|2020/02/28T12:42:23|75.6|86.7|
|1313|1582922243690|2020/02/28T12:37:23|75.6|87.2|
|1312|1582921943693|2020/02/28T12:32:23|75.6|88.7|
|1311|1582921643687|2020/02/28T12:27:23|75.6|95.2|
|1310|1582921343685|2020/02/28T12:22:23|75.6|93.1|
|1309|1582921043686|2020/02/28T12:17:23|75.6|90.2|
|1308|1582920743687|2020/02/28T12:12:23|75.6|99.4|
|1307|1582920443685|2020/02/28T12:07:23|75.5|105.5|
|1306|1582920143683|2020/02/28T12:02:23|75.5|105.3|
|1305|1582919843683|2020/02/28T11:57:23|75.4|106.4|
|1304|1582919543680|2020/02/28T11:52:23|75.4|105.1|
|1303|1582919243679|2020/02/28T11:47:23|75.3|103.3|
|1302|1582918943678|2020/02/28T11:42:23|75.2|99.2|
|1301|1582918643676|2020/02/28T11:37:23|75.2|103.4|
|1300|1582918343672|2020/02/28T11:32:23|75.1|103.6|
|1299|1582918043655|2020/02/28T11:27:23|75.1|102.5|
|1298|1582917743668|2020/02/28T11:22:23|75|100.1|
|1297|1582917443668|2020/02/28T11:17:23|75|100|
|1296|1582917143666|2020/02/28T11:12:23|75|99.5|
|1295|1582916843668|2020/02/28T11:07:23|75|98.5|
|1294|1582916543645|2020/02/28T11:02:23|74.9|100.7|
|1293|1582916243660|2020/02/28T10:57:23|74.9|104|
|1292|1582915943659|2020/02/28T10:52:23|74.8|104.6|
|1291|1582915643657|2020/02/28T10:47:23|74.6|100.8|
|1290|1582915343658|2020/02/28T10:42:23|74.6|98|
|1289|1582915043659|2020/02/28T10:37:23|74.6|96.9|
|1288|1582914743659|2020/02/28T10:32:23|74.6|97.9|
|1287|1582914443659|2020/02/28T10:27:23|74.6|101.6|
|1286|1582914143658|2020/02/28T10:22:23|74.5|100.1|
|1285|1582913843658|2020/02/28T10:17:23|74.5|99.6|
|1284|1582913543656|2020/02/28T10:12:23|74.4|106.3|
|1283|1582913243656|2020/02/28T10:07:23|74.4|103.7|
|1282|1582912943657|2020/02/28T10:02:23|74.4|99.7|
|1281|1582912643655|2020/02/28T09:57:23|74.3|95.6|
|1280|1582912343653|2020/02/28T09:52:23|74.2|97.6|
|1279|1582912043654|2020/02/28T09:47:23|74.2|98.6|
|1278|1582911743651|2020/02/28T09:42:23|74.1|103.2|
|1277|1582911443652|2020/02/28T09:37:23|74|108.4|
|1276|1582911143650|2020/02/28T09:32:23|73.8|105.5|
|1275|1582910843649|2020/02/28T09:27:23|73.7|95.2|
|1274|1582910543649|2020/02/28T09:22:23|73.7|93.1|
|1273|1582910243648|2020/02/28T09:17:23|73.8|88.9|
|1272|1582909943640|2020/02/28T09:12:23|74.1|85.7|
|1271|1582909643639|2020/02/28T09:07:23|74.1|88.1|
|1270|1582909343639|2020/02/28T09:02:23|73.8|87.3|
|1269|1582909043629|2020/02/28T08:57:23|73.8|86.9|
|1268|1582908743636|2020/02/28T08:52:23|73.9|88.8|
|1267|1582908443636|2020/02/28T08:47:23|73.9|89.3|
|1266|1582908143636|2020/02/28T08:42:23|74|93.2|
|1265|1582907843637|2020/02/28T08:37:23|74|95.8|
|1264|1582907543632|2020/02/28T08:32:23|73.9|92.2|
|1263|1582907243634|2020/02/28T08:27:23|73.9|101.2|
|1262|1582906943633|2020/02/28T08:22:23|73.9|97.2|
|1261|1582906643633|2020/02/28T08:17:23|73.9|95.6|
|1260|1582906343634|2020/02/28T08:12:23|74.1|92.8|
|1259|1582906043632|2020/02/28T08:07:23|73.8|90.9|
|1258|1582905743633|2020/02/28T08:02:23|73.7|95.1|
|1257|1582905443632|2020/02/28T07:57:23|73.4|89.5|
|1256|1582905143634|2020/02/28T07:52:23|73.1|78.5|
|1255|1582904843633|2020/02/28T07:47:23|73.2|75.6|
|1254|1582904543633|2020/02/28T07:42:23|73.3|75.5|
|1253|1582904243633|2020/02/28T07:37:23|73.4|75.8|
|1252|1582903943631|2020/02/28T07:32:23|72.4|72.6|
|1251|1582903643629|2020/02/28T07:27:23|72.4|71.8|
|1250|1582903343628|2020/02/28T07:22:23|72.5|71.8|
|1249|1582903043626|2020/02/28T07:17:23|72.9|72.3|
|1248|1582902743628|2020/02/28T07:12:23|73.2|73.6|
|1247|1582902443632|2020/02/28T07:07:23|72.4|72|
|1246|1582902143626|2020/02/28T07:02:23|72.1|70.4|
|1245|1582901843626|2020/02/28T06:57:23|72.2|70.4|
|1244|1582901543624|2020/02/28T06:52:23|72.5|70.5|
|1243|1582901243625|2020/02/28T06:47:23|72.7|71|
|1242|1582900943626|2020/02/28T06:42:23|73.1|72.3|
|1241|1582900643624|2020/02/28T06:37:23|72.1|70.3|
|1240|1582900343623|2020/02/28T06:32:23|72|69.5|
|1239|1582900043623|2020/02/28T06:27:23|72.3|69.6|
|1238|1582899743623|2020/02/28T06:22:23|72.5|69.9|
|1237|1582899443624|2020/02/28T06:17:23|72.9|70.7|
|1236|1582899143619|2020/02/28T06:12:23|73.3|72.4|
|1235|1582898843621|2020/02/28T06:07:23|72.4|70.5|
|1234|1582898543621|2020/02/28T06:02:23|72.2|69.3|
|1233|1582898243620|2020/02/28T05:57:23|72.3|69.6|
|1232|1582897943619|2020/02/28T05:52:23|72.6|69.8|
|1231|1582897643618|2020/02/28T05:47:23|72.8|70.5|
|1230|1582897343619|2020/02/28T05:42:23|73.3|72.1|
|1229|1582897043617|2020/02/28T05:37:23|72.5|70.7|
|1228|1582896743619|2020/02/28T05:32:23|72.1|69.4|
|1227|1582896443616|2020/02/28T05:27:23|72.3|69.5|
|1226|1582896143618|2020/02/28T05:22:23|72.5|69.7|
|1225|1582895843616|2020/02/28T05:17:23|72.7|70.2|
|1224|1582895543616|2020/02/28T05:12:23|73.1|71.3|
|1223|1582895243617|2020/02/28T05:07:23|72.8|71.3|
|1222|1582894943617|2020/02/28T05:02:23|72.2|69.4|
|1221|1582894643617|2020/02/28T04:57:23|72.3|69.4|
|1220|1582894343614|2020/02/28T04:52:23|72.5|69.6|
|1219|1582894043614|2020/02/28T04:47:23|72.6|69.7|
|1218|1582893743602|2020/02/28T04:42:23|72.9|70.2|
|1217|1582893443612|2020/02/28T04:37:23|73.3|71.2|
|1216|1582893143613|2020/02/28T04:32:23|73.3|72.2|
|1215|1582892843612|2020/02/28T04:27:23|72.4|69.5|
|1214|1582892543612|2020/02/28T04:22:23|72.6|69.5|
|1213|1582892243611|2020/02/28T04:17:23|72.7|69.6|
|1212|1582891943612|2020/02/28T04:12:23|73|70|
|1211|1582891643610|2020/02/28T04:07:23|73.2|70.7|
|1210|1582891343610|2020/02/28T04:02:23|73.8|72.3|
|1209|1582891043609|2020/02/28T03:57:23|73|71.1|
|1208|1582890743608|2020/02/28T03:52:23|72.7|69.4|
|1207|1582890443609|2020/02/28T03:47:23|72.8|69.5|
|1206|1582890143607|2020/02/28T03:42:23|72.9|69.8|
|1205|1582889843606|2020/02/28T03:37:23|73.2|70.4|
|1204|1582889543605|2020/02/28T03:32:23|73.7|71.4|
|1203|1582889243604|2020/02/28T03:27:23|73.8|72.7|
|1202|1582888943604|2020/02/28T03:22:23|73.1|70.2|
|1201|1582888643606|2020/02/28T03:17:23|72.7|69.4|
|1200|1582888343605|2020/02/28T03:12:23|72.8|69.4|
|1199|1582888043606|2020/02/28T03:07:23|72.8|69.4|
|1198|1582887743604|2020/02/28T03:02:23|72.9|69.6|

Thank you very much for your time.

zenofmud · 3 March 2020 10:12

We are going to work thru this bit by bit. With that in mind:

Why do you have a json node after the sqlite node?
If you put a debug node on the change node after the join node, what do you see?
is what you see correct (compare it to the example)

spyder0552 · 3 March 2020 21:28

Chiming back in on this.
So it looks like your SQL Lite query is getting the data that you need. You have a ID field from the database, a UTC time value, Human Time value and what appears to be a high and low value to plot on the chart.

Your challenge is going to be getting that data into a format that the chart will accept and plot. I feel your pain as I do this a lot and still struggle with it (great at SQL...not so good with NodeRed lol).

As you stated the format is:

[{
"series": ["A", "B", "C"],
"data": [
    [{ "x": 1504029632890, "y": 5 },
     { "x": 1504029636001, "y": 4 },
     { "x": 1504029638656, "y": 2 }
    ],
    [{ "x": 1504029633514, "y": 6 },
     { "x": 1504029636622, "y": 7 },
     { "x": 1504029639539, "y": 6 }
    ],
    [{ "x": 1504029634400, "y": 7 },
     { "x": 1504029637959, "y": 7 },
     { "x": 1504029640317, "y": 7 }
    ]
],
"labels": [""]
}]

So, you are going to need something like:
[{
"Series": ["A"],
"data": [
[msg.payload.[UTC,HP]]
],
"labels": [""]
}]

That should get you going. You are very close, don't give up.

NODE777 · 4 March 2020 04:13

@zenofmud

Well your question is very interesting for me who I'm learning.!

Why do you have a json node after the sqlite node?

When I was struggling to get read on the ui-Table node, I put that node but I didn't realize it didn't have a useful effect for that node

If you put a debug node on the change node after the join node, what do you see?

After sqlite node there're " two paths" on the last flow I posted , one of them going through the JASON node (Which I remove) and the other one is going to the change node.
I did not put any join node originally on the last flow I posted , but I added one of them ( trying to interpret your question ), and after done that and add a debug node after the "change node" I see that the debug node show me the following payload:

The following es the config.for join node

Join node

This is the flow modified as you required ????


3/3/2020, 7:22:29 PM[node: 1cdec6ce.5cf779](http://127.0.0.1:1880/#)SELECT UTC , hp FROM hvac4 WHERE UTC BETWEEN 1582514397054 AND 1582600797054 : msg : Object

object

payload: array[1]

0: object

series: "HVACHP"

values: array[558]

[0 … 9]

0: 2020-02-24T04:28:20.357Z

1: 1582518501868

2: 2020-02-24T04:28:22.953Z

3: 1582518503997

4: 2020-02-24T04:28:24.856Z

5: 1582518505501

6: 2020-02-24T04:34:20.997Z

7: 1582519061579

8: 2020-02-24T04:39:20.999Z

9: 2020-02-24T04:44:20.998Z

[10 … 19]

[20 … 29]

[30 … 39]

[40 … 49]

[50 … 59]

[60 … 69]

[70 … 79]

[80 … 89]

[90 … 99]

[100 … 109]

[110 … 119]

[120 … 129]

[130 … 139]

[140 … 149]

[150 … 159]

[160 … 169]

[170 … 179]

[180 … 189]

[190 … 199]

[200 … 209]

[210 … 219]

[220 … 229]

[230 … 239]

[240 … 249]

[250 … 259]

[260 … 269]

[270 … 279]

270: 1582597488966

271: 1582597788969

272: 1582598088982

273: 1582598388979

274: 1582598688996

275: 1582598989009

276: 1582599289013

277: 1582599589024

278: 1582600675911

279: 74.5

[280 … 289]

280: 74.5

281: 74.5

282: 74.5

283: 74.5

284: 74.5

285: 74.5

286: 74.4

287: 75.3

288: 68.4

289: 68.3

[290 … 299]

[300 … 309]

[310 … 319]

[320 … 329]

[330 … 339]

[340 … 349]

[350 … 359]

[360 … 369]

[370 … 379]

[380 … 389]

[390 … 399]

[400 … 409]

400: 63.2

401: 65.1

402: 63.9

403: 63.4

404: 63.3

405: 63.5

406: 65.7

407: 64.9

408: 64.7

409: 65.2

[410 … 419]

[420 … 429]

[430 … 439]

[440 … 449]

[450 … 459]

[460 … 469]

[470 … 479]

[480 … 489]

[490 … 499]

[500 … 509]

[510 … 519]

[520 … 529]

[530 … 539]

[540 … 549]

[550 … 557]

labels: array[1]

socketid: "JJQlu5tDyKnfZF0HAAAD"

_msgid: "55529383.376b0c"

topic: " SELECT UTC , hp FROM hvac4 WHERE UTC BETWEEN 1582514397054 AND 1582600797054 "

As I can see is a set of arrays that only contain timestamps and other set that just contain values............. but is evident that those set of data does not follow the correct format to inject on the chart

Well at this point, if I done what you request on your second question I believe that I am currently at the depart point of this post.
I'm looking forward for your commentaries and thank you again.

NODE777 · 4 March 2020 04:16

@spyder0552

Hi,
Thank you for your encouragement , I'm not going to give up

Colin · 4 March 2020 07:20

At the risk of taking this thread off say a tangent can I ask whether the database is one that has already existed for some purpose and you are now trying to draw charts from it, or whether the purpose of the database is in order to draw the charts? If the purpose is in order to see the charts then there may be alternative approaches that will give you a much better results.

Topic		Replies	Views
Node-Red / sqlite / Charts General	18	2099	8 May 2021
SQLite -> Dashboard Graph: I am not getting it Dashboard	4	3099	21 November 2018
Put a variable data into Json code? General	4	3494	28 November 2018
How to retrieve integer value from SQLite for display and comparison General	8	1411	18 April 2020
SQLITE data to Charts - Various Issues General database	13	1914	11 March 2022

From sqlite to json format

Related topics