@mauri-0 Ja dazu kenn ich die Influx DB zu wenig, Du musst halt mal schauen, wie dort die Datenstruktur ist. Ich nutze mysql und da sieht die Datenstruktur so aus:
b9a1b4d0-5f74-4e26-97ae-75df8177bdbc-image.png
Der timestamp wird also einfach als Unix Timestamp gespeichert.
Das SQL Kommando musst Du bei der mysql Node über das Topic eingeben:
Das schaut bei mir so aus und wird über eine Template Node gefüllt:
SELECT `id`, `ts`, `val` FROM `iobroker`.`ts_string` WHERE `id` = 10 AND `val` IS NOT NULL AND `ts` BETWEEN 1651148231204 AND 1651166787428 ORDER BY `ts`
Tagesstart und Tagesende hole ich mir über die Moments Bibliothek.
Das Herzstück der mysql Abfragen also between
ist das :
36455f5f-b230-48b6-82b4-6abe104ce36d-image.png
Spoiler
[
{
"id": "0979e2426ef5caa8",
"type": "change",
"z": "e88aa902136812d3",
"name": "",
"rules": [
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "{\t \"Beginn\": $moment($flowContext('fDay.start')).format('DD.MM.YYYY HH:mm:ss'),\t \"Ende\": $moment($flowContext('fDay.end')).format('DD.MM.YYYY HH:mm:ss')\t }",
"tot": "jsonata"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 610,
"y": 660,
"wires": [
[
"e7d53a0e1fa76841",
"17bbce13ce808556"
]
]
},
{
"id": "17bbce13ce808556",
"type": "change",
"z": "e88aa902136812d3",
"name": "setze Zeitspanne",
"rules": [
{
"t": "set",
"p": "start",
"pt": "msg",
"to": "fDay.start",
"tot": "flow"
},
{
"t": "set",
"p": "end",
"pt": "msg",
"to": "fDay.end",
"tot": "flow"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 850,
"y": 660,
"wires": [
[
"fe4836ac61fc9367",
"7d31081fd229aefe",
"d04bca6c69e97fbc"
]
]
},
{
"id": "fe4836ac61fc9367",
"type": "template",
"z": "e88aa902136812d3",
"name": "SQL get events",
"field": "topic",
"fieldType": "msg",
"format": "handlebars",
"syntax": "mustache",
"template": "SELECT `id`, `ts`, `val` FROM `iobroker`.`ts_string` WHERE `id` = 8 AND `val` IS NOT NULL AND `ts` BETWEEN {{start}} AND {{end}} ORDER BY `ts`",
"output": "str",
"x": 1060,
"y": 660,
"wires": [
[
"fd9d53f790673ffb",
"7e73716770fcf354"
]
]
}
]
Mit der Change Node - kannst Du zu jedem beliebigen Datum, das Ende des jeweiligen Tages als Timestamp auszugeben.
fd7681ae-f620-4c90-981e-c84577ccccc6-image.png
das gleiche geht auch mit dem Tagesstart
Hier mal ein Beispiel wie Du anhand eines Datums die exakte Start und Endezeit in ms umrechnest:
b8b066c9-9fef-4d6d-96fe-b621b71ed1fd-image.png
Spoiler
[
{
"id": "07be0c5ec40e0eb0",
"type": "change",
"z": "e88aa902136812d3",
"name": "",
"rules": [
{
"t": "set",
"p": "payload.start",
"pt": "msg",
"to": "$moment(payload.start, 'DD.MM.YYYY').startOf('day').format('x') ~> $number()",
"tot": "jsonata"
},
{
"t": "set",
"p": "payload.ende",
"pt": "msg",
"to": "$moment(payload.ende, 'DD.MM.YYYY').endOf('day').format('x') ~> $number()",
"tot": "jsonata"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 490,
"y": 1440,
"wires": [
[
"efa3e2f3acb871ee",
"fb75cb0fb18f8c66"
]
]
},
{
"id": "f03ec762aafdaf0f",
"type": "inject",
"z": "e88aa902136812d3",
"name": "Zeitraum in ms",
"props": [
{
"p": "payload"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "{\"start\":\"01.01.2022\",\"ende\":\"01.02.2022\"}",
"payloadType": "json",
"x": 260,
"y": 1440,
"wires": [
[
"d037c8d97b51894f",
"07be0c5ec40e0eb0"
]
]
},
{
"id": "d037c8d97b51894f",
"type": "debug",
"z": "e88aa902136812d3",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 470,
"y": 1380,
"wires": []
},
{
"id": "efa3e2f3acb871ee",
"type": "debug",
"z": "e88aa902136812d3",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 690,
"y": 1380,
"wires": []
},
{
"id": "fb75cb0fb18f8c66",
"type": "change",
"z": "e88aa902136812d3",
"name": "",
"rules": [
{
"t": "set",
"p": "payload.start",
"pt": "msg",
"to": "$moment(payload.start, 'x').format('DD.MM.YYYY HH:mm:ss') ",
"tot": "jsonata"
},
{
"t": "set",
"p": "payload.ende",
"pt": "msg",
"to": "$moment(payload.ende, 'x').format('DD.MM.YYYY HH:mm:ss')",
"tot": "jsonata"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 710,
"y": 1440,
"wires": [
[
"1c5374be913740f9"
]
]
},
{
"id": "1c5374be913740f9",
"type": "debug",
"z": "e88aa902136812d3",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 910,
"y": 1380,
"wires": []
}
]
