Amazing, I have figured out strange thing. I previously adjusted server time to local time zone in my Linux VM. That is causing the events date and time wrong show in table. Now i just redo that and configured the timezone as Universal, and this solves the problem. Now the events date and time is correctly shown on NR Dashboard table.
So in a nutshell the timestamp (with Universal TZ) is inserted in DB at server but timestamp shown in table is as per local time.
Somehow it works as required. But later on I need to figure out and understand how did that happens. Thanks madhouse for your support.
Thanks for your recommendation. I followed the steps to insert the event data and time through function (Javascrypt) along with payload data. One exception is that I inserted the data and time as a text in MySql. Tried a lot of date and time formats but all those didn't work. So at last i have to choose text as data type in MySql for Date & Time field. And that works absolutely fine until now. Below is the function code;
let temp = msg.payload["home/temperature5"];
let hum = msg.payload["home/humidity5"];
let hi = msg.payload["home/heatindex5"];
var date = new Date();
var current_date = date.getFullYear() + "-" + (date.getMonth() + 1) + "-" + date.getDate();
var current_time = date.getHours() + ":" + date.getMinutes() + ":" + date.getSeconds();
var date_time = current_date + " " + current_time;
msg.topic = `INSERT INTO SensorData5(temperature, humidity, heatindex, event_time, device_id) VALUES(${temp},${hum},${hi},'${date_time}','Home 5')`;
return msg;
Still strange as I am forced to use "Text" as data type in both places i.e., in Form and in Database. It must be Date & Time. Anyways I keep exploring the proper way of doing this thing. Thanks
I recommend you read the mysql node built in help and the readme. It demonstrates how to use parameters instead of building a SQL string (SQL strings are vulnerable to SQL injection hacks)
That's ok. You're kind of half way there. There's two ways you can store the date in the table and you've already identified both of them. The simple way is to store them as a text string as it can be formatted and simply pulled as itself to be displayed in stuff like tables or some other human readable object. Once obtained, there's nothing else that needs to be done. The problem that you run into is that if you have to change how the time is displayed or if you need to extract particulars of the timestamp, you may not have all the information.
The other way is to store the epoch time. Instead of storing a string, you're storing an inordinately large number in the form of something like milliseconds since a given point in time (Jan 1, 1970 00:00:00.00). While storage is somewhat simple (storing a number), the conversion is complicated. Now you have to do stuff like invoke server side rules to have it transmit the time stored as a string. Or you have to convert the number into local time, which Javascript does so with built-in routines.
let temp = msg.payload["home/temperature5"];
let hum = msg.payload["home/humidity5"];
let hi = msg.payload["home/heatindex5"];
var date = new Date();
var date_time = date.getTime();
msg.topic = `INSERT INTO SensorData5(temperature, humidity, heatindex, event_time, device_id) VALUES(${temp},${hum},${hi},'${date_time}','Home 5')`;
return msg;
Then once you retrieve the date (supposing we extract the date from the database as the variable date_time):
var date = new Date(date_time);
var display_date = date.toLocaleString();
And now you have a text string formatted in the locale style defined in the OS.
But perhaps this is already what you're doing...
Hopefully it's what you need it to be at this point and you're all well and good!
Thanks, I have tried the initial part of your suggestion to store date and time as epoch time, and being able to insert date time only by changing the event_date data type from DATETIME/ timestamp to VARCHAR (13).
Now the date and time shown in dashboard table is epoch time. I am not sur e how to convert this to local time.
Moreover the second part of your suggestion which includes the below mentioned code is requiring bit more elaboration as to where this code needs to be used?
var date = new Date(date_time);
var display_date = date.toLocaleString();
That is the code you will place in where you read in the values. There are a couple places you can put it. Since Tabulator is not reading in your database information, you can simply place a function between the database reader and the injection into Tabulator and use that function to convert the times that come through it to a locale based time. This is probably the simplest setup, though it makes all the time formatted to the system locale Node-Red is running on.
Another option that makes it truly local based to the client is by using a mutator in Tabulator. You can find more about mutators here. Follow the instructions on how and where to place mutator definitions and calls and your table will display the time according to the system locale of the client instead of the server.
The last option I can think of is placing it under the tableBuilt event. This way, you perform the action of going through all the cells in the time column and reformat them to the locale time.
There's a number of ways to do it. If you're not worried about different locales, I would suggest doing a function between the output of the database query node and the input of the table, assuming you don't already have one. If you do have one, just add that code in to parse the timestamp before it's passed to the table.
Interesting new thing to learn. Thanks for sharing and it will surely take some to understand but sure i will do as it seems greatly interesting. I will share the outcome. Once agian thanks a lot.
