# Issue with Time Stamp while displaying MySQL table data on dashboard

**URL:** <https://discourse.nodered.org/t/issue-with-time-stamp-while-displaying-mysql-table-data-on-dashboard/56559>\
**Category:** Dashboard\
**Created:** [13 January 2022 01:51 UTC](https://discourse.nodered.org/t/issue-with-time-stamp-while-displaying-mysql-table-data-on-dashboard/56559 "2022-01-13T01:51:56Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![saxenaramesh](https://avatars.discourse-cdn.com/v4/letter/s/919ad9/32.png) [@saxenaramesh](https://discourse.nodered.org/u/saxenaramesh)\
**Post date:** [13 January 2022 01:51 UTC](https://discourse.nodered.org/t/issue-with-time-stamp-while-displaying-mysql-table-data-on-dashboard/56559/1 "2022-01-13T01:51:56Z")

</div>

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:

```auto
[{"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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/0/b09b3bbbcbd9756114f8e64d5efc0610f1a4a66c.png)

while, screen shot from nodered dashboard is as below:

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/9/79639d0241829775c7b53f78d5109bc925e6092b.png)

---

<div class="post-metadata">

**Author:** ![Bobo](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bobo/32/8401_2.png) [@Bobo](https://discourse.nodered.org/u/Bobo)\
**Post date:** [13 January 2022 02:30 UTC](https://discourse.nodered.org/t/issue-with-time-stamp-while-displaying-mysql-table-data-on-dashboard/56559/2 "2022-01-13T02:30:58Z")

</div>

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](https://flows.nodered.org/node/node-red-contrib-moment) 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/](https://day.js.org/), which will do the same.

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [13 January 2022 05:38 UTC](https://discourse.nodered.org/t/issue-with-time-stamp-while-displaying-mysql-table-data-on-dashboard/56559/3 "2022-01-13T05:38:36Z")

</div>

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 )

> [@saxenaramesh](#):
>
> Time Stamp value in mysql database is in my localtime +05:30 but on dashboard, it gets converted to UTC

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.

---

<div class="post-metadata">

**Author:** ![saxenaramesh](https://avatars.discourse-cdn.com/v4/letter/s/919ad9/32.png) [@saxenaramesh](https://discourse.nodered.org/u/saxenaramesh)\
**Post date:** [14 January 2022 15:53 UTC](https://discourse.nodered.org/t/issue-with-time-stamp-while-displaying-mysql-table-data-on-dashboard/56559/4 "2022-01-14T15:53:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![saxenaramesh](https://avatars.discourse-cdn.com/v4/letter/s/919ad9/32.png) [@saxenaramesh](https://discourse.nodered.org/u/saxenaramesh)\
**Post date:** [14 January 2022 15:53 UTC](https://discourse.nodered.org/t/issue-with-time-stamp-while-displaying-mysql-table-data-on-dashboard/56559/5 "2022-01-14T15:53:47Z")

</div>

Thanks UnborN, I will give it a try.

---

<div class="post-metadata">

**Author:** ![saxenaramesh](https://avatars.discourse-cdn.com/v4/letter/s/919ad9/32.png) [@saxenaramesh](https://discourse.nodered.org/u/saxenaramesh)\
**Post date:** [14 January 2022 16:24 UTC](https://discourse.nodered.org/t/issue-with-time-stamp-while-displaying-mysql-table-data-on-dashboard/56559/6 "2022-01-14T16:24:40Z")

</div>

This worked really well. Thanks a lot.

---

<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:** [28 January 2022 16:25 UTC](https://discourse.nodered.org/t/issue-with-time-stamp-while-displaying-mysql-table-data-on-dashboard/56559/7 "2022-01-28T16:25:10Z")

</div>

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