Variable in Node "influxdb in"

[hinti]

Hallo!

Ich verwende die Node "influxdb in" und habe ein Problem mit einer Variable.
Bei

SELECT sum("Pool_vom_Netz") / 120000 FROM "Bilanz" WHERE time > now() -31d

funktioniert es. Ich möchte aber die (-31d) Tage in einer Variable verpacken.
Das was ich im Netz gefunden habe funktioniert leider nicht.

SELECT sum("Pool_vom_Netz") / 120000 FROM "Bilanz" WHERE time > now() '-${payload.Tage}d'

Wie schaut die Variable richtig aus?
Hier der Flow:

Spoiler

[
{
"id": "c4adbc7357d003d1",
"type": "tab",
"label": "Flow 1",
"disabled": false,
"info": "",
"env": []
},
{
"id": "45b9ad6228447b62",
"type": "inject",
"z": "c4adbc7357d003d1",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 200,
"y": 160,
"wires": [
[
"473b104681483dc8"
]
]
},
{
"id": "473b104681483dc8",
"type": "function",
"z": "c4adbc7357d003d1",
"name": "Monatstage",
"func": "var msg = {};\n\n// ------------- Aktuellen Monat herausfinden -------------------------\nvar Tage;\nvar now = new Date();\nvar month = now.getMonth() + 1;\n\nif (month == 1) {Tage = 31}\nif (month == 2) { Tage = 28 }\nif (month == 3) { Tage = 31}\nif (month == 4) { Tage = 30 }\nif (month == 5) { Tage = 31}\nif (month == 6) { Tage = 30 }\nif (month == 7) { Tage = 31 }\nif (month == { Tage = 31 }\nif (month == 9) { Tage = 30 }\nif (month == 10) { Tage = 31 }\nif (month == 11) { Tage = 30 }\nif (month == 12) { Tage = 31 }\n// --------------------------------------------------------------------\n\n\nmsg.payload =\n{\n Tage:Tage,\n}\nreturn [msg];",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 370,
"y": 160,
"wires": [
[
"e73be277cb1cc425"
]
]
},
{
"id": "e73be277cb1cc425",
"type": "influxdb in",
"z": "c4adbc7357d003d1",
"influxdb": "655abc6ff1dd28f5",
"name": "Abfrage Zählerst. Energie",
"query": "SELECT sum("Pool_vom_Netz") / 120000 FROM "Bilanz" WHERE time > now() '-${payload.Tage}d'",
"rawOutput": true,
"precision": "",
"retentionPolicy": "",
"org": "",
"x": 590,
"y": 160,
"wires": [
[]
]
},
{
"id": "655abc6ff1dd28f5",
"type": "influxdb",
"hostname": "10.1.1.113",
"port": "8086",
"protocol": "http",
"database": "whirlpool",
"name": "whirlpool",
"usetls": false,
"tls": "",
"influxdbVersion": "1.x",
"url": "http://localhost:8086",
"rejectUnauthorized": true
}
]

Danke.

[mickym]

Mit Deinem Flow kann man nichts anfangen weil Du das nicht in Code-Tags gepackt hast.

Wenn Du das so reinkopierst interpretiert der Browser zeichen und man kann es nicht importieren.

Du setzt einfach Deine komplette msg.query zusammen. Was heißt Variable ist es ein Eigenschaft des Nachrichtenobjektes oder nicht?

Spoiler

[
   {
       "id": "a0f099e28f8b85fc",
       "type": "inject",
       "z": "5ad55a7b0d4580eb",
       "name": "",
       "props": [
           {
               "p": "payload"
           }
       ],
       "repeat": "",
       "crontab": "",
       "once": false,
       "onceDelay": 0.1,
       "topic": "",
       "payload": "{\"Tage\":31}",
       "payloadType": "json",
       "x": 340,
       "y": 2740,
       "wires": [
           [
               "2b43c618076c4c06",
               "21b7ebf788d45e74"
           ]
       ]
   },
   {
       "id": "2b43c618076c4c06",
       "type": "change",
       "z": "5ad55a7b0d4580eb",
       "name": "",
       "rules": [
           {
               "t": "set",
               "p": "query",
               "pt": "msg",
               "to": "'SELECT sum(\"Pool_vom_Netz\") / 120000 FROM \"Bilanz\" WHERE time > now() -' & payload.Tage & 'd'",
               "tot": "jsonata"
           }
       ],
       "action": "",
       "property": "",
       "from": "",
       "to": "",
       "reg": false,
       "x": 540,
       "y": 2740,
       "wires": [
           [
               "5cdbd6f2567e8a20"
           ]
       ]
   },
   {
       "id": "21b7ebf788d45e74",
       "type": "debug",
       "z": "5ad55a7b0d4580eb",
       "name": "payload",
       "active": true,
       "tosidebar": true,
       "console": false,
       "tostatus": false,
       "complete": "payload",
       "targetType": "msg",
       "statusVal": "",
       "statusType": "auto",
       "x": 520,
       "y": 2680,
       "wires": []
   },
   {
       "id": "5cdbd6f2567e8a20",
       "type": "debug",
       "z": "5ad55a7b0d4580eb",
       "name": "query",
       "active": true,
       "tosidebar": true,
       "console": false,
       "tostatus": false,
       "complete": "query",
       "targetType": "msg",
       "statusVal": "",
       "statusType": "auto",
       "x": 730,
       "y": 2680,
       "wires": []
   }
]

Wie gesagt woher Deine Tage kommen - kann ich nicht sagen - da man Deinen Flow nicht importierten kann. Das Nachrichtenobjekt siehst Du ja - falls Du anderen Kontext nimmst, entweder in das Nachrichtenobjekt aufnehmen oder halt mit den entsprechenden Funktionen darauf zugreifen.

[hinti]

@mickym
Danke für die Antwort.
Hier nochmals der Flow:

Spoiler

[
   {
       "id": "72c36d015644d984",
       "type": "tab",
       "label": "Flow 2",
       "disabled": false,
       "info": "",
       "env": []
   },
   {
       "id": "8a88b6fdb45f100c",
       "type": "inject",
       "z": "72c36d015644d984",
       "name": "",
       "props": [
           {
               "p": "payload"
           },
           {
               "p": "topic",
               "vt": "str"
           }
       ],
       "repeat": "",
       "crontab": "",
       "once": false,
       "onceDelay": 0.1,
       "topic": "",
       "payload": "",
       "payloadType": "date",
       "x": 120,
       "y": 140,
       "wires": [
           [
               "a9316343deec6c53"
           ]
       ]
   },
   {
       "id": "a9316343deec6c53",
       "type": "function",
       "z": "72c36d015644d984",
       "name": "Monatstage",
       "func": "var msg = {};\n\n// ------------- Aktuellen Monat herausfinden -------------------------\nvar Tage;\nvar now = new Date();\nvar month = now.getMonth() + 1;\n\nif (month == 1) {Tage = 31}\nif (month == 2) { Tage = 28 }\nif (month == 3) { Tage = 31}\nif (month == 4) { Tage = 30 }\nif (month == 5) { Tage = 31}\nif (month == 6) { Tage = 30 }\nif (month == 7) { Tage = 31 }\nif (month == 8) { Tage = 31 }\nif (month == 9) { Tage = 30 }\nif (month == 10) { Tage = 31 }\nif (month == 11) { Tage = 30 }\nif (month == 12) { Tage = 31 }\n// --------------------------------------------------------------------\n\n\nmsg.payload =\n{\n   Tage:Tage,\n}\nreturn [msg];",
       "outputs": 1,
       "noerr": 0,
       "initialize": "",
       "finalize": "",
       "libs": [],
       "x": 290,
       "y": 140,
       "wires": [
           [
               "7e701c0656398ec3",
               "1ea910c9a8ca6a24"
           ]
       ]
   },
   {
       "id": "7e701c0656398ec3",
       "type": "influxdb in",
       "z": "72c36d015644d984",
       "influxdb": "655abc6ff1dd28f5",
       "name": "Abfrage Zählerst. Energie",
       "query": "SELECT sum(\"Pool_vom_Netz\") / 120000 FROM \"Bilanz\" WHERE time > now() '-${payload.Tage}d'",
       "rawOutput": true,
       "precision": "",
       "retentionPolicy": "",
       "org": "",
       "x": 510,
       "y": 140,
       "wires": [
           [
               "31afc62051488e86"
           ]
       ]
   },
   {
       "id": "31afc62051488e86",
       "type": "debug",
       "z": "72c36d015644d984",
       "name": "payload",
       "active": true,
       "tosidebar": true,
       "console": false,
       "tostatus": false,
       "complete": "payload",
       "targetType": "msg",
       "statusVal": "",
       "statusType": "auto",
       "x": 720,
       "y": 140,
       "wires": []
   },
   {
       "id": "1ea910c9a8ca6a24",
       "type": "debug",
       "z": "72c36d015644d984",
       "name": "payload",
       "active": true,
       "tosidebar": true,
       "console": false,
       "tostatus": false,
       "complete": "payload",
       "targetType": "msg",
       "statusVal": "",
       "statusType": "auto",
       "x": 460,
       "y": 80,
       "wires": []
   },
   {
       "id": "655abc6ff1dd28f5",
       "type": "influxdb",
       "hostname": "10.1.1.113",
       "port": "8086",
       "protocol": "http",
       "database": "whirlpool",
       "name": "whirlpool",
       "usetls": false,
       "tls": "",
       "influxdbVersion": "1.x",
       "url": "http://localhost:8086",
       "rejectUnauthorized": true
   }
]

Mit der Variable direkt im Query hab ich das Problem. Mit deiner Lösung mit dem Change Node funktioniert es jetzt

Spoiler

[
   {
       "id": "f6bfa45d44848e0b",
       "type": "tab",
       "label": "Flow 3",
       "disabled": false,
       "info": "",
       "env": []
   },
   {
       "id": "d6218fe1418c8ae9",
       "type": "inject",
       "z": "f6bfa45d44848e0b",
       "name": "",
       "props": [
           {
               "p": "payload"
           },
           {
               "p": "topic",
               "vt": "str"
           }
       ],
       "repeat": "",
       "crontab": "",
       "once": false,
       "onceDelay": 0.1,
       "topic": "",
       "payload": "",
       "payloadType": "date",
       "x": 100,
       "y": 260,
       "wires": [
           [
               "2e25ecba20d95814"
           ]
       ]
   },
   {
       "id": "2e25ecba20d95814",
       "type": "function",
       "z": "f6bfa45d44848e0b",
       "name": "Monatstage",
       "func": "var msg = {};\n\n// ------------- Aktuellen Monat herausfinden -------------------------\nvar Tage;\nvar now = new Date();\nvar month = now.getMonth() + 1;\n\nif (month == 1) {Tage = 31}\nif (month == 2) { Tage = 28 }\nif (month == 3) { Tage = 31}\nif (month == 4) { Tage = 30 }\nif (month == 5) { Tage = 31}\nif (month == 6) { Tage = 30 }\nif (month == 7) { Tage = 31 }\nif (month == 8) { Tage = 31 }\nif (month == 9) { Tage = 30 }\nif (month == 10) { Tage = 31 }\nif (month == 11) { Tage = 30 }\nif (month == 12) { Tage = 31 }\n// --------------------------------------------------------------------\n\n\nmsg.payload =\n{\n   Tage:Tage,\n}\nreturn [msg];",
       "outputs": 1,
       "noerr": 0,
       "initialize": "",
       "finalize": "",
       "libs": [],
       "x": 270,
       "y": 260,
       "wires": [
           [
               "0374cab1d63834c4",
               "42e42ac5275c90ae"
           ]
       ]
   },
   {
       "id": "42e42ac5275c90ae",
       "type": "debug",
       "z": "f6bfa45d44848e0b",
       "name": "payload",
       "active": false,
       "tosidebar": true,
       "console": false,
       "tostatus": false,
       "complete": "payload",
       "targetType": "msg",
       "statusVal": "",
       "statusType": "auto",
       "x": 440,
       "y": 220,
       "wires": []
   },
   {
       "id": "0374cab1d63834c4",
       "type": "change",
       "z": "f6bfa45d44848e0b",
       "name": "",
       "rules": [
           {
               "t": "set",
               "p": "query",
               "pt": "msg",
               "to": "'SELECT sum(\"Pool_vom_Netz\") / 120000 FROM \"Bilanz\" WHERE time > now() -' & payload.Tage & 'd'",
               "tot": "jsonata"
           }
       ],
       "action": "",
       "property": "",
       "from": "",
       "to": "",
       "reg": false,
       "x": 460,
       "y": 260,
       "wires": [
           [
               "9005b1ef0601da83",
               "7e1adada9963614b"
           ]
       ]
   },
   {
       "id": "7e1adada9963614b",
       "type": "debug",
       "z": "f6bfa45d44848e0b",
       "name": "query",
       "active": false,
       "tosidebar": true,
       "console": false,
       "tostatus": false,
       "complete": "query",
       "targetType": "msg",
       "statusVal": "",
       "statusType": "auto",
       "x": 630,
       "y": 220,
       "wires": []
   },
   {
       "id": "9005b1ef0601da83",
       "type": "influxdb in",
       "z": "f6bfa45d44848e0b",
       "influxdb": "655abc6ff1dd28f5",
       "name": "Abfrage Zählerst. Energie",
       "query": "msg.query",
       "rawOutput": true,
       "precision": "",
       "retentionPolicy": "",
       "org": "",
       "x": 690,
       "y": 260,
       "wires": [
           [
               "af64830ef5a1d939",
               "833e8db5cdba66d3"
           ]
       ]
   },
   {
       "id": "af64830ef5a1d939",
       "type": "debug",
       "z": "f6bfa45d44848e0b",
       "name": "payload",
       "active": true,
       "tosidebar": true,
       "console": false,
       "tostatus": false,
       "complete": "payload",
       "targetType": "msg",
       "statusVal": "",
       "statusType": "auto",
       "x": 840,
       "y": 220,
       "wires": []
   },
   {
       "id": "833e8db5cdba66d3",
       "type": "function",
       "z": "f6bfa45d44848e0b",
       "name": "E_Whirlpool_Monat_v_Netz",
       "func": "var msg1 = {};\n\nvar Pool_vom_Netz = msg.payload.results[0].series[0].values[0][1];\n\n\nmsg1.payload =\n{\n\nPool_vom_Netz_Monat: parseFloat(Pool_vom_Netz.toFixed(2)),\n  \n\n}\n\n\nreturn [msg1];",
       "outputs": 1,
       "noerr": 0,
       "initialize": "",
       "finalize": "",
       "libs": [],
       "x": 940,
       "y": 260,
       "wires": [
           [
               "8014820292266bd7"
           ]
       ]
   },
   {
       "id": "8014820292266bd7",
       "type": "debug",
       "z": "f6bfa45d44848e0b",
       "name": "payload",
       "active": true,
       "tosidebar": true,
       "console": false,
       "tostatus": false,
       "complete": "payload",
       "targetType": "msg",
       "statusVal": "",
       "statusType": "auto",
       "x": 1020,
       "y": 220,
       "wires": []
   },
   {
       "id": "655abc6ff1dd28f5",
       "type": "influxdb",
       "hostname": "10.1.1.113",
       "port": "8086",
       "protocol": "http",
       "database": "whirlpool",
       "name": "whirlpool",
       "usetls": false,
       "tls": "",
       "influxdbVersion": "1.x",
       "url": "http://localhost:8086",
       "rejectUnauthorized": true
   }
]

Kann man sich die change Node sparren, und die Variable direkt in die Query stecken?

[mickym]

@hinti Nein msg.query musst du vorher komplett zusammenbauen. Dafür ist es ja da. Ansonsten in der Node halt nur konstante Ausdrücke

[hinti]

@mickym
OK, Super. Danke für deine Hilfe.

[mickym]

@hinti wenn du mehrere Parameter hast, kannst du die msg.query auch mit einer Template Node zusammenbauen. Ist zwar nicht anders als mit einer Change-Node, vielleicht nur ein wenig übersichtlicher.