UI-Table timestamp with correct TZ

Dashboard
1

Howdy,
Loving Node-Red and that I have a dashboard table pulling from a DB using only 2 lines of code.

I grab sensor data from various ESP32 nodes via MQTT and stick them in a MariaDB.

I have Node Red running on a Rasberry PI V1.2.6
Time is stored as a standard Linux TimeStamp format in the DB.

Simple table and phpmyadmin displays it as:

TimeStamp Source Topic DataValue

2021-01-28 15:00:29 frontdoor temperature 31

  • many rows.
    Note that phpmyadmin converts the TimeStamp to my TimeZone for an easy to read format.

Then in node-red function node I have:
msg.topic = 'Select * FROM sensors ORDER BY TIMESTAMP DESC LIMIT 12;';
return msg;

I connect this function node to a mysql node then to the UI_Table and all works great and
was very simple/clean. Display is just like the table above except the TimeStamp is displayed in UTC which is hard to read. ie. 2021-01-28T20:53:30.00Z

Is there an easy way in this stream to convert the Timestamp to display using my TimeZone NY.
This is what is fed to the table:

array[12]
[0 … 9]
0: object
TimeStamp: "2021-01-28T21:32:31.000Z"
Source: "frontdoor"
Topic: "temperature"
DataValue: 28
1: object
TimeStamp: "2021-01-28T21:31:31.000Z"
Source: "frontdoor"
Topic: "temperature"
DataValue: 28

Thanks,
-steve

2

Well hard-to-read is relative. Actually it is very easy to read once you get used to it. And no problems with month-first vs date-first. You can convert in a function node with const ts = new Date(TimeStamp).

3

Maybe you can try making the tz convertion directly from sql with

SELECT CONVERT_TZ(TimeStamp,'+00:00','+04:00') AS TIMESTAMP, Source, Topic, DataValue FROM sensors ORDER BY TIMESTAMP DESC LIMIT 12   ;

Date & Time Function - CONVERT_TZ

adjust +04:00 according to your tz offset

4

Nice - thanks

5

thanks - appreciated

6

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