From sqlite to json format

General
1

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.

2

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.

3

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

4

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.

Debug Node after a Selection clause
Debug Node after a Selection clause1341×448 35.6 KB 

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

5

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.

  1. add the chart you want.
  2. 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.
  3. 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.

6

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

7

@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 .

Seletct a historical parameter
Seletct a historical parameter1142×151 15.7 KB

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.

8

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

9

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?

10

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?

11

Well, I did partially my assignment :slight_smile:
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.

Flow
Flow1142×308 28.7 KB

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.

12

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

13

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

db snapshoot
db snapshoot1229×564 36.7 KB
:

Thank you.

14

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)

15

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.

16

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)
17

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.

18

@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 ????

Flow
Flow1298×321 34.2 KB


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.

19

@spyder0552

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

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.