Issue with Time Stamp while displaying MySQL table data on dashboard

Dashboard
1

Dear Friends,

I am using template node to get table in node-red dashboard and then populate that table with data from my mysql database. Everything is good except that time stamp changes to UTC. Time Stamp value in mysql database is in my localtime +05:30 but on dashboard, it gets converted to UTC. I want this to stay same as my database. Please suggest how to resolve this?
my flow example is as below:

[{"id":"39dcd3f3.252d3c","type":"ui_template","z":"bd7047e5.39bbb8","group":"e82e4dc2.6ebd5","name":"","order":3,"width":18,"height":13,"format":"<html>\n<head>\n    \n<style>\n    #history {\n      font-family: \"Arial\";\n        border-collapse: collapse;\n        width: 100%;\n        \n        }\n        \n        #history td, #history th {\n        border: 1px solid #ddd;\n        padding: 8px;\n        \n        }\n        \n        #history tr:nth-child(even){background-color: #black;}\n        #history tr:hover {background-color: #green;}\n        #history th {\n        padding-top: 12px;\n        padding-bottom: 12px;\n        text-align: center;\n        background-color: #24B819;\n        color: white;\n        \n        }\n        </style>\n        \n        </head>\n        <body>\n        <h4></h4>\n        <br>\n        <table id=\"history\" border=\"1\">\n            <tr align=\"center\">\n\n                 <th>ID</th>\n                 <th>Time</th>\n                 <th>Tank1_EC</th>\n                 <th>Tank1_PH</th>\n                 <th>Tank1_W_Temp</th>\n                 <th>Tank2_EC</th>\n                 <th>Tank2_PH</th>\n                 <th>Tank2_W_Temp</th>\n                 <th>Tank3_EC</th>\n                 <th>Tank3_PH</th>\n                 <th>Tank3_W_Temp</th>\n                 <th>LUX_LEVEL</th>\n                 <th>GB_AIR_HUMIDITY</th>\n                 <th>GB_AIR_TEMP</th>\n                 <th>NFT_AIR_HUMIDITY</th>\n                 <th>NFT_AIR_TEMP</th>\n                \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>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":720,"y":2040,"wires":[[]]},{"id":"4d1dafcd.06d39","type":"function","z":"bd7047e5.39bbb8","name":"","func":"msg.topic = 'select * from farm_live_values order by time_stamp desc limit 100;'\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":300,"y":2040,"wires":[["c6c65d64.476ff"]]},{"id":"c6c65d64.476ff","type":"mysql","z":"bd7047e5.39bbb8","mydb":"9429fa82f4809a7c","name":"Query","x":480,"y":2040,"wires":[["39dcd3f3.252d3c"]]},{"id":"a542425d.8ea81","type":"inject","z":"bd7047e5.39bbb8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"60","crontab":"","once":false,"onceDelay":0.1,"topic":"","payloadType":"date","x":130,"y":2040,"wires":[["4d1dafcd.06d39"]]},{"id":"e82e4dc2.6ebd5","type":"ui_group","name":"Default","tab":"fdcbd8c9.6ff0a8","order":1,"disp":true,"width":18,"collapse":false},{"id":"9429fa82f4809a7c","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"ALMUS_FARM","tz":"+05:30","charset":"UTF8"},{"id":"fdcbd8c9.6ff0a8","type":"ui_tab","name":"Farm Data","icon":"dashboard","order":3,"disabled":false,"hidden":false}]

database screen shot:
image

while, screen shot from nodered dashboard is as below:

image

2

Probably the easiest and quickest way to achieve what you want is to either use the moment node node-red-contrib-moment (node) - Node-RED to change the format to what you want just prior to creating the table, OR to use a function node and import one of the more modern date libraries like day.js https://day.js.org/, which will do the same.

1 Like
3

Hello Ramesh .. along with Bobo's suggestions .. you could try to do the convertion straight from mySql

SELECT *, CONVERT_TZ(time_stamp,'+00:00','+02:00') AS converted_timeZone FROM farm_live_values order by time_stamp desc limit 100;

( adjust '+02:00' accordingly )

i doubt that its the dashboard that is doing any conversion .. the template node just shows what it gets from msg.payload coming from mySql. Confirm the type of the time_stamp column, the server time and what tz your time is when you do the inserts. also what sql was run to generate the database screenshot.

you should INSERT it as UTC from the beginning and do the conversion to whatever TZ later.

1 Like
4

Thanks Bobo for your reply. I will try it and inform you the results.

5

Thanks UnborN, I will give it a try.

6

This worked really well. Thanks a lot.

7

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