Drop-down-node displays wrong date

Hello all,

I have a problem with the drop-down-node.
I want to display a date and time in the dorp-down-node. It displays the time +2h.
The debug-node and also the "normal" text-node displays the correct time.
I don't know what's wrong my flow. (see below).
Can anyone help?

[
    {
        "id": "6747fdd7d7f05f64",
        "type": "function",
        "z": "2fbf1c672ac8808f",
        "name": "All",
        "func": "//var executed = global.get('executedAll') || false;\n\nvar start_date = new Date(global.get('start_date'));\nvar end_date = new Date(global.get('end_date'));\nvar unit = global.get('unit')\n\nvar formattedStartDate = start_date.getFullYear() + '-' + ('0' + (start_date.getMonth() + 1)).slice(-2) + '-' + ('0' + start_date.getDate()).slice(-2);\nvar formattedEndDate = end_date.getFullYear() + '-' + ('0' + (end_date.getMonth() + 1)).slice(-2) + '-' + ('0' + end_date.getDate()).slice(-2);\n\nmsg.topic = \"SELECT Datum, Grund, Druck_Vorher, Druck_Nachher FROM opccleaning WHERE Ja = 1 AND Date(Datum) BETWEEN Date(STR_TO_DATE('\" + formattedStartDate + \"', '%Y-%m-%d')) AND Date(STR_TO_DATE('\" + formattedEndDate + \"', '%Y-%m-%d')) AND unit = '\" + unit + \"'\"; \n\nreturn msg;\n\n",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 650,
        "y": 160,
        "wires": [
            [
                "a773858c40e2dd5c"
            ]
        ]
    },
    {
        "id": "a773858c40e2dd5c",
        "type": "mysql",
        "z": "2fbf1c672ac8808f",
        "mydb": "01f3806be55a308a",
        "name": "OPCCleaning",
        "x": 800,
        "y": 160,
        "wires": [
            [
                "7961a6d6f80119e8"
            ]
        ]
    },
    {
        "id": "7961a6d6f80119e8",
        "type": "function",
        "z": "2fbf1c672ac8808f",
        "name": "data drop-down",
        "func": "\nmsg.options = [];//create empty array\n\nfor(let i = 0; i < msg.payload.length; i++)\n{\n    let row = msg.payload[i]; //get the row\n    let opt = {};//make new opt object\n    opt[row.Datum] =new Date(row.Datum).toISOString().slice(0,-1); \n    msg.options.push(opt);//add the opt to array   \n}\nreturn msg;\n\n\n\n/*for(let i = 0; i < msg.payload.length; i++)\n{\n    let row = msg.payload[i]; // Holen Sie sich die Zeile\n    if(row.Datum) { \n        let opt = {}; // Erstellen Sie ein neues opt-Objekt\n        let dateWithoutTimezone = new Date(row.Datum).toISOString().slice(0, -1); // Entfernen Sie die Zeitzone aus dem Datum\n        opt[row.Datum] = dateWithoutTimezone; \n        msg.options.push(opt); // Fügen Sie das opt zum Array hinzu   \n    }\n}\nreturn msg;\n*/",
        "outputs": 1,
        "timeout": "",
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 200,
        "y": 240,
        "wires": [
            [
                "4c7b4463f6dd1361",
                "459d2624ee277bfa"
            ]
        ]
    },
    {
        "id": "4c7b4463f6dd1361",
        "type": "ui_dropdown",
        "z": "2fbf1c672ac8808f",
        "name": "",
        "label": "",
        "tooltip": "",
        "place": "Select option",
        "group": "9150c527f9189d07",
        "order": 24,
        "width": 15,
        "height": 1,
        "passthru": true,
        "multiple": false,
        "options": [
            {
                "label": "",
                "value": "",
                "type": "str"
            }
        ],
        "payload": "",
        "topic": "topic",
        "topicType": "msg",
        "className": "",
        "x": 380,
        "y": 280,
        "wires": [
            []
        ]
    },
    {
        "id": "01f3806be55a308a",
        "type": "MySQLdatabase",
        "name": "messwerte",
        "host": "127.0.0.1",
        "port": "3306",
        "db": "messwerte",
        "tz": "'local'",
        "charset": "UTF8"
    },
    {
        "id": "9150c527f9189d07",
        "type": "ui_group",
        "name": "Cleaning",
        "tab": "6da2769d8f177f9c",
        "order": 1,
        "disp": false,
        "width": 19,
        "collapse": false,
        "className": ""
    },
    {
        "id": "6da2769d8f177f9c",
        "type": "ui_tab",
        "name": "Reinigung",
        "icon": "fa-eraser",
        "order": 2,
        "disabled": false,
        "hidden": false
    }
]

Hard to know what is happening as you have supplied no data, remove the database node and replace with an inject node, you can add the array of objects you DB returns in the JSON input { }. You only need add two or three objects, as that would be more than enough to see the data format.

Sorry for the missing data the database supplies.
The data from the database looks like:

[{"Datum":"2024-05-14T11:07:54.000Z","Grund":"Zeit","Druck_Vorher":13.2,"Druck_Nachher":11.1},{"Datum":"2024-05-14T11:33:00.000Z","Grund":"Zeit","Druck_Vorher":12.5,"Druck_Nachher":9.2}]

Seem ok to me. Also because the text-node displays the Date/Time correctly.

What are you seeing in the dropdown and in the debug and text nodes for that data?

I solved the problem. Via a few detours, but it is finally working.

[
    {
        "id": "1e37df5177ca7426",
        "type": "mysql",
        "z": "2fbf1c672ac8808f",
        "mydb": "01f3806be55a308a",
        "name": "OPCCleaning",
        "x": 800,
        "y": 100,
        "wires": [
            [
                "8d86dce4352ce159",
                "ce5bf3bcbe7eb55c"
            ]
        ]
    },
    {
        "id": "c955e2b408f4bcdd",
        "type": "function",
        "z": "2fbf1c672ac8808f",
        "name": "Last",
        "func": "\nvar start_date = new Date(global.get('start_date'));\nvar end_date = new Date(global.get('end_date'));\nvar unit = global.get('unit')\n\nvar formattedStartDate = start_date.getFullYear() + '-' + ('0' + (start_date.getMonth() + 1)).slice(-2) + '-' + ('0' + start_date.getDate()).slice(-2);\nvar formattedEndDate = end_date.getFullYear() + '-' + ('0' + (end_date.getMonth() + 1)).slice(-2) + '-' + ('0' + end_date.getDate()).slice(-2);\n\nmsg.topic = \"SELECT COUNT(Ja) AS AnzahlReinigungen, MAX(Datum) AS LastDate FROM opccleaning WHERE Ja = 1 AND Date(Datum) BETWEEN Date(STR_TO_DATE('\" + formattedStartDate + \"', '%Y-%m-%d')) AND Date(STR_TO_DATE('\" + formattedEndDate + \"', '%Y-%m-%d')) AND unit = '\" + unit + \"'\"; \n\nreturn msg;",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 650,
        "y": 100,
        "wires": [
            [
                "1e37df5177ca7426"
            ]
        ]
    },
    {
        "id": "6747fdd7d7f05f64",
        "type": "function",
        "z": "2fbf1c672ac8808f",
        "name": "All",
        "func": "//var executed = global.get('executedAll') || false;\n\nvar start_date = new Date(global.get('start_date'));\nvar end_date = new Date(global.get('end_date'));\nvar unit = global.get('unit')\n\nvar formattedStartDate = start_date.getFullYear() + '-' + ('0' + (start_date.getMonth() + 1)).slice(-2) + '-' + ('0' + start_date.getDate()).slice(-2);\nvar formattedEndDate = end_date.getFullYear() + '-' + ('0' + (end_date.getMonth() + 1)).slice(-2) + '-' + ('0' + end_date.getDate()).slice(-2);\n\nmsg.topic = \"SELECT Datum, Grund, Druck_Vorher, Druck_Nachher FROM opccleaning WHERE Ja = 1 AND Date(Datum) BETWEEN Date(STR_TO_DATE('\" + formattedStartDate + \"', '%Y-%m-%d')) AND Date(STR_TO_DATE('\" + formattedEndDate + \"', '%Y-%m-%d')) AND unit = '\" + unit + \"'\"; \n\nreturn msg;\n\n",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 650,
        "y": 160,
        "wires": [
            [
                "a773858c40e2dd5c"
            ]
        ]
    },
    {
        "id": "a773858c40e2dd5c",
        "type": "mysql",
        "z": "2fbf1c672ac8808f",
        "mydb": "01f3806be55a308a",
        "name": "OPCCleaning",
        "x": 800,
        "y": 160,
        "wires": [
            [
                "7961a6d6f80119e8"
            ]
        ]
    },
    {
        "id": "ce5bf3bcbe7eb55c",
        "type": "moment",
        "z": "2fbf1c672ac8808f",
        "name": "Zeit",
        "topic": "",
        "input": "payload[0].LastDate",
        "inputType": "msg",
        "inTz": "Europe/Berlin",
        "adjAmount": "2",
        "adjType": "hours",
        "adjDir": "add",
        "format": "ISO",
        "locale": "de-DE",
        "output": "payload",
        "outputType": "msg",
        "outTz": "Europe/Berlin",
        "x": 1010,
        "y": 120,
        "wires": [
            [
                "7e7ac38c938228e8"
            ]
        ]
    },
    {
        "id": "7e7ac38c938228e8",
        "type": "ui_text",
        "z": "2fbf1c672ac8808f",
        "group": "9150c527f9189d07",
        "order": 20,
        "width": 17,
        "height": 1,
        "name": "",
        "label": "Letzte Reinigung: ",
        "format": "{{msg.payload}}",
        "layout": "row-spread",
        "className": "",
        "style": false,
        "font": "",
        "fontSize": 16,
        "color": "#000000",
        "x": 1210,
        "y": 120,
        "wires": []
    },
    {
        "id": "7961a6d6f80119e8",
        "type": "function",
        "z": "2fbf1c672ac8808f",
        "name": "data drop-down",
        "func": "global.set('pay', msg.payload);\nmsg.options = [];//create empty array\n\nfor(let i = 0; i < msg.payload.length; i++)\n{\n    let row = msg.payload[i]; //get the row\n    let opt = {};//make new opt object\n    let dateInUTC = new Date(row.Datum).toISOString();\n    opt[row.Datum] = dateInUTC; \n    msg.options.push(opt);//add the opt to array   \n}\nreturn msg;",
        "outputs": 1,
        "timeout": "",
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 200,
        "y": 240,
        "wires": [
            [
                "4c7b4463f6dd1361"
            ]
        ]
    },
    {
        "id": "4c7b4463f6dd1361",
        "type": "ui_dropdown",
        "z": "2fbf1c672ac8808f",
        "name": "",
        "label": "",
        "tooltip": "",
        "place": "Select option",
        "group": "9150c527f9189d07",
        "order": 24,
        "width": 15,
        "height": 1,
        "passthru": true,
        "multiple": false,
        "options": [
            {
                "label": "",
                "value": "",
                "type": "str"
            }
        ],
        "payload": "",
        "topic": "",
        "topicType": "str",
        "className": "",
        "x": 380,
        "y": 240,
        "wires": [
            [
                "2ea03fdcb5b90141"
            ]
        ]
    },
    {
        "id": "2ea03fdcb5b90141",
        "type": "function",
        "z": "2fbf1c672ac8808f",
        "name": "find reason",
        "func": "let originalPayload = global.get('pay');\nlet selectedDate = msg.payload;\nnode.warn(originalPayload);\nvar reason ;\nvar preBefore;\nvar preAfter;\n\nfor (let i = 0; i < originalPayload.length; i++) {\n    let row = originalPayload[i]; //get the row\n    let rowDate = new Date(row.Datum).getTime(); //convert to Date and get time value\n    let selectedDateTime = new Date(selectedDate).getTime(); //convert selectedDate to Date and get time value\n    \n    if (rowDate === selectedDateTime) {\n        reason = row.Grund;\n        preBefore = row.Druck_Vorher;\n        preAfter = row.Druck_Nachher;\n        break;\n    }\n}\n\nvar result = {\n    \"Grund\": reason,\n    \"Druck_Vorher\": preBefore,\n    \"Druck_Nachher\": preAfter\n};\n\nmsg.payload = result;\nreturn msg;",
        "outputs": 1,
        "timeout": "",
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 570,
        "y": 240,
        "wires": [
            [
                "b45cc01d35a08ed5",
                "b9e896fa1606bed6",
                "e67012092c912250"
            ]
        ]
    },
    {
        "id": "01f3806be55a308a",
        "type": "MySQLdatabase",
        "name": "messwerte",
        "host": "127.0.0.1",
        "port": "3306",
        "db": "messwerte",
        "tz": "",
        "charset": "UTF8"
    },
    {
        "id": "9150c527f9189d07",
        "type": "ui_group",
        "name": "Cleaning",
        "tab": "6da2769d8f177f9c",
        "order": 1,
        "disp": false,
        "width": 19,
        "collapse": false,
        "className": ""
    },
    {
        "id": "6da2769d8f177f9c",
        "type": "ui_tab",
        "name": "Reinigung",
        "icon": "fa-eraser",
        "order": 2,
        "disabled": false,
        "hidden": false
    }
]

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.