Good morning, today I was practicing with node red, and a doubt arose, it turns out that the UI template has an html code of a table to display mysql data, it happens that the date comes out fine but the time does not come out the same as that is in my database, I have doubts about that part, does anyone know why it is generated like this? or am I missing something for it to be generated correctly? .
Would be good for you to share what you get and what you expect.
1 Like
good day sorry for the delay, of course look what happens is that the range of hours and minutes accepts them from 00:00:00 - 23:59:59, but even so it shows me incorrect times, I attach my flow and screenshots
[
{
"id": "943a3c9c514b1f29",
"type": "ui_date_picker",
"z": "86704f9b99183b4b",
"name": "",
"label": "Inicio",
"group": "bd91d6d28d4a7275",
"order": 1,
"width": 0,
"height": 0,
"passthru": true,
"topic": "",
"topicType": "str",
"className": "",
"x": 110,
"y": 200,
"wires": [
[
"de0f66ed649ae3bf",
"1673df2ce32645e9"
]
]
},
{
"id": "1673df2ce32645e9",
"type": "moment",
"z": "86704f9b99183b4b",
"name": "",
"topic": "",
"input": "payload",
"inputType": "msg",
"inTz": "America/Bogota",
"adjAmount": "1",
"adjType": "days",
"adjDir": "subtract",
"format": "",
"locale": "es-ES",
"output": "payload",
"outputType": "msg",
"outTz": "America/Bogota",
"x": 340,
"y": 200,
"wires": [
[
"be075a8df2b5a940",
"f5568907cde1882c"
]
]
},
{
"id": "be075a8df2b5a940",
"type": "debug",
"z": "86704f9b99183b4b",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 510,
"y": 120,
"wires": []
},
{
"id": "de0f66ed649ae3bf",
"type": "debug",
"z": "86704f9b99183b4b",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 190,
"y": 120,
"wires": []
},
{
"id": "f5568907cde1882c",
"type": "string",
"z": "86704f9b99183b4b",
"name": "",
"methods": [
{
"name": "left",
"params": [
{
"type": "num",
"value": "10"
}
]
}
],
"prop": "payload",
"propout": "payload",
"object": "msg",
"objectout": "msg",
"x": 570,
"y": 200,
"wires": [
[
"3d6ab5665db0198e",
"c9ade27ad8ec30fe"
]
]
},
{
"id": "3d6ab5665db0198e",
"type": "debug",
"z": "86704f9b99183b4b",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 770,
"y": 120,
"wires": []
},
{
"id": "c9ade27ad8ec30fe",
"type": "function",
"z": "86704f9b99183b4b",
"name": "",
"func": "var date1= msg.payload;\nvar date2= date1 +\" 00:00:00\";\nglobal.set('Inicio',date2);\nmsg.payload=date2;\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 760,
"y": 200,
"wires": [
[]
]
},
{
"id": "39ced2bd158ef218",
"type": "ui_date_picker",
"z": "86704f9b99183b4b",
"name": "",
"label": "Final",
"group": "bd91d6d28d4a7275",
"order": 2,
"width": 0,
"height": 0,
"passthru": true,
"topic": "",
"topicType": "str",
"className": "",
"x": 110,
"y": 260,
"wires": [
[
"69f08110b038b334"
]
]
},
{
"id": "69f08110b038b334",
"type": "moment",
"z": "86704f9b99183b4b",
"name": "",
"topic": "",
"input": "payload",
"inputType": "msg",
"inTz": "America/Bogota",
"adjAmount": "1",
"adjType": "days",
"adjDir": "subtract",
"format": "",
"locale": "es-ES",
"output": "payload",
"outputType": "msg",
"outTz": "America/Bogota",
"x": 340,
"y": 260,
"wires": [
[
"627481c5f855bc3c",
"5fde162e8b816620"
]
]
},
{
"id": "627481c5f855bc3c",
"type": "string",
"z": "86704f9b99183b4b",
"name": "",
"methods": [
{
"name": "left",
"params": [
{
"type": "num",
"value": "10"
}
]
}
],
"prop": "payload",
"propout": "payload",
"object": "msg",
"objectout": "msg",
"x": 570,
"y": 260,
"wires": [
[
"c3fcb48b79886b5f",
"d5aa88d72071a83a"
]
]
},
{
"id": "d5aa88d72071a83a",
"type": "function",
"z": "86704f9b99183b4b",
"name": "",
"func": "var date1= msg.payload;\nvar date2= date1 +\" 23:59:59\";\nglobal.set('Final',date2);\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 760,
"y": 260,
"wires": [
[]
]
},
{
"id": "4c819baca84f7155",
"type": "debug",
"z": "86704f9b99183b4b",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 710,
"y": 360,
"wires": []
},
{
"id": "5405cce4fb215be9",
"type": "ui_button",
"z": "86704f9b99183b4b",
"name": "",
"group": "bd91d6d28d4a7275",
"order": 6,
"width": 0,
"height": 0,
"passthru": false,
"label": "Filtrar",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "",
"payloadType": "str",
"topic": "",
"topicType": "str",
"x": 110,
"y": 420,
"wires": [
[
"37e98d1737e15ede"
]
]
},
{
"id": "4dcad8d3ea6a3288",
"type": "comment",
"z": "86704f9b99183b4b",
"name": "Ordenar Datos",
"info": "",
"x": 120,
"y": 80,
"wires": []
},
{
"id": "d452ef9a538bef7f",
"type": "mysql",
"z": "86704f9b99183b4b",
"mydb": "fb05e811b8b0da75",
"name": "",
"x": 490,
"y": 420,
"wires": [
[
"4c819baca84f7155",
"b85007866b7097b5"
]
]
},
{
"id": "37e98d1737e15ede",
"type": "function",
"z": "86704f9b99183b4b",
"name": "",
"func": "var from = global.get(\"Inicio\");\nvar to = global.get(\"Final\");\n\nmsg.topic = 'SELECT * FROM plc.trupal WHERE `Fecha` BETWEEN \"'+from+'\" AND \"'+to+'\";';\nreturn msg;\n",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 300,
"y": 420,
"wires": [
[
"d452ef9a538bef7f"
]
]
},
{
"id": "b85007866b7097b5",
"type": "ui_template",
"z": "86704f9b99183b4b",
"group": "4f96a77756f4ce93",
"name": "",
"order": 1,
"width": 23,
"height": 8,
"format": "<style>\n #history {\n font-family: \"Arial\";\n border-collapse: collapse;\n width: 100%;\n }\n \n #history td, #history th {\n border: 1px solid #ddd;\n padding: 8px;\n }\n #history tr:nth-child(even){background-color: #A8EEF8;}\n \n #history tr:hover {background-color: #40aeea;}\n \n #history th {\n padding-top: 12px;\n padding-bottom: 12px;\n text-align: center;\n background-color: #696969;\n color: white;\n }\n \n \n </style>\n \n <table id=\"history\" border=\"1\">\n <tr align=\"center\">\n \n <th>id</th>\n <th>Fecha</th>\n <th>OEE_ME</th>\n <th>RECHAZO</th>\n <th>ENERGIAT1</th>\n <th>ENERGIA_T2</th>\n <th>ENERGIAT3</th>\n <th>ENERGIAPROMEDIO</th>\n <th>ENERGIA_TOTAL_T</th>\n <th>ENERGIAACTUAL</th>\n \n </tr>\n <tbody>\n <tr align=\"center\" ng-repeat=\"row in msg.payload\">\n <td ng-repeat=\"item in row\" >{{item}}</td>\n </tr>\n </tbody>\n </table>\n \n ",
"storeOutMessages": true,
"fwdInMessages": true,
"resendOnRefresh": true,
"templateScope": "local",
"className": "",
"x": 700,
"y": 420,
"wires": [
[
"e3adf3d939877caa"
]
]
},
{
"id": "5fde162e8b816620",
"type": "debug",
"z": "86704f9b99183b4b",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 470,
"y": 320,
"wires": []
},
{
"id": "c3fcb48b79886b5f",
"type": "debug",
"z": "86704f9b99183b4b",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 750,
"y": 300,
"wires": []
},
{
"id": "e3adf3d939877caa",
"type": "debug",
"z": "86704f9b99183b4b",
"name": "",
"active": true,
"tosidebar": true,
"console": true,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 920,
"y": 420,
"wires": []
},
{
"id": "bd91d6d28d4a7275",
"type": "ui_group",
"name": "Produccion",
"tab": "4e037359f216eb68",
"order": 1,
"disp": true,
"width": "6",
"collapse": false,
"className": ""
},
{
"id": "fb05e811b8b0da75",
"type": "MySQLdatabase",
"name": "",
"host": "127.0.0.1",
"port": "3306",
"db": "plc",
"tz": "",
"charset": "UTF8"
},
{
"id": "4f96a77756f4ce93",
"type": "ui_group",
"name": "Datos por Fecha",
"tab": "4e037359f216eb68",
"order": 3,
"disp": true,
"width": "24",
"collapse": true,
"className": ""
},
{
"id": "4e037359f216eb68",
"type": "ui_tab",
"name": "Trupal",
"icon": "dashboard",
"order": 16,
"disabled": false,
"hidden": false
}
]
in my dashboard it marks at hour 22 when it should come out at 17 as seen in my mysql, it is the only wrong thing that it throws, everything else looks correct
That looks like a timezone issue. You are displaying in UTC on the page - perhaps the data in the DB is in a local tz?
You should always store and process timestamps in UTC with local only used for convenient user input and display.
that was what I thought last time, but in my mysql the timestap is configured with ON UPDATE CURRENT_TIMESTAMP, so that it takes the time zone in UTC, and in my flow I format it with the node node-red-contrib-moment, so that It also remains in UTC, even so when I consult it it looks like in the photo, in addition to the fact that in the moment node it gives me the option of giving it different time formats, it does not give me the hours or it comes out as in the photo only with the bad time.
Can you please put debug nodes on the input to and output from moment node and the output from your function node and share the resulting output so that we can see the data coming out?
It's the only thing it shows me, but to see the entire flow process, insert a debug into each one, and I'll also attach the context where the global is stored.
If you were to take this number you are showing:
And turn it into a JavaScript timestamp with new Date(1656028800000). You would soon realise that its time component is 00:00:00.
A very simple flow demonstrates this:
[{"id":"a646e17bf55fe1d0","type":"moment","z":"ff1a7711.244f48","name":"","topic":"","input":"","inputType":"msg","inTz":"","adjAmount":0,"adjType":"days","adjDir":"add","format":"","locale":"","output":"","outputType":"msg","outTz":"","x":540,"y":1960,"wires":[["dad17c3c527d8aab"]]},{"id":"59d16edc716528b3","type":"inject","z":"ff1a7711.244f48","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"1656028800000","payloadType":"num","x":320,"y":1960,"wires":[["a646e17bf55fe1d0"]]},{"id":"dad17c3c527d8aab","type":"debug","z":"ff1a7711.244f48","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":790,"y":1960,"wires":[]}]
When I change the output from moment to be this:
Time zone in Bogotá, Bogota, Colombia (GMT-5)
I get:
Which is correct. With newer versions of node.js in a function node, you could also do this:
const mydate = new Date(1656028800000)
let options = {
year: 'numeric', month: 'numeric', day: 'numeric',
hour: 'numeric', minute: 'numeric', second: 'numeric',
hour12: false,
timeZone: 'America/Bogota'
}
msg.payload = new Intl.DateTimeFormat('en-GB', options).format(mydate)
return msg
And you would get the same answer. 23/06/2022, 19:00:00.
In other words, the input TZ is taken as UTC and the output as 'America/Bogota'. Minus 5 hours.
No matter what I set the moment node to, I cannot reproduce what you are seeing.
What TZ is your server set to?
1 Like
The time zone of my server is Colombian time, it is in UTC -5, the example that you show me I also did the practice but it does not take the range of the hour, I do not know how to make it take the range of hours which must be quite a day.
Not sure what is happening but I recommend that you simplify things. You seem to have multiple nodes but are only trying to convert the timestamp to a string which the moment node will do for you anyway as I demonstrated.
Maybe also try passing the source data through a function node with the code shown above to see what you get.
1 Like
Good morning, sorry for the delay in responding, I've been busy, I understand what you mean, I'll do the code again and simplify it with a function, I also did the test from another pc, with MYSQL workbench and it shows me the time and date but in my separate project I have to generate a pdf it happens that it generates it wrong for me, that is, the template thing happens, it shows me the time wrong, I will do what you advised me I will simplify it and I will comment on the result again, thanks for your patience since I am new at this.
Good morning mate, I simplified the code and tried the same way as the example that you attached to me, and it didn't work, it doesn't show any data in the table, besides I chose the date of the 17th of this month and it gave me the date of the 18th, Even so, it does not show data now, I am sorry for the inconvenience, thanks for the time you dedicated to teach me, I will look for more information to be able to visualize that data on the dashboard, have a great day.
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.
