# Drop-down-node displays wrong date

**URL:** <https://discourse.nodered.org/t/drop-down-node-displays-wrong-date/87983>\
**Category:** Dashboard\
**Tags:** node-red-dashboard, database\
**Created:** [15 May 2024 14:47 UTC](https://discourse.nodered.org/t/drop-down-node-displays-wrong-date/87983 "2024-05-15T14:47:03Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Absolut\_FloxD](https://avatars.discourse-cdn.com/v4/letter/a/ea5d25/32.png) [@Absolut\_FloxD](https://discourse.nodered.org/u/Absolut_FloxD)\
**Post date:** [15 May 2024 14:47 UTC](https://discourse.nodered.org/t/drop-down-node-displays-wrong-date/87983/1 "2024-05-15T14:47:03Z")

</div>

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?

```auto
[
    {
        "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
    }
]

```

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [15 May 2024 14:54 UTC](https://discourse.nodered.org/t/drop-down-node-displays-wrong-date/87983/2 "2024-05-15T14:54:58Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Absolut\_FloxD](https://avatars.discourse-cdn.com/v4/letter/a/ea5d25/32.png) [@Absolut\_FloxD](https://discourse.nodered.org/u/Absolut_FloxD)\
**Post date:** [16 May 2024 05:53 UTC](https://discourse.nodered.org/t/drop-down-node-displays-wrong-date/87983/3 "2024-05-16T05:53:48Z")

</div>

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

```auto
[{"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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [16 May 2024 07:06 UTC](https://discourse.nodered.org/t/drop-down-node-displays-wrong-date/87983/4 "2024-05-16T07:06:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Absolut\_FloxD](https://avatars.discourse-cdn.com/v4/letter/a/ea5d25/32.png) [@Absolut\_FloxD](https://discourse.nodered.org/u/Absolut_FloxD)\
**Post date:** [16 May 2024 08:12 UTC](https://discourse.nodered.org/t/drop-down-node-displays-wrong-date/87983/5 "2024-05-16T08:12:55Z")

</div>

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

```auto
[
    {
        "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
    }
]

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [15 June 2024 08:13 UTC](https://discourse.nodered.org/t/drop-down-node-displays-wrong-date/87983/6 "2024-06-15T08:13:29Z")

</div>

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