Sql query of Datetime column returning UTC time not the table data

General
1

Hi,
I got an issue where I store a timestamp into a datetime table in sql and get result like this
image

But then the problem is when I select query this data into nodered ui, it return the data in UTC format
image

How I can make this data as same as in the sql table?
Thank you

2

Timestamps in databases should always be in UTC. This avoids a great many errors and date/time related problems. They should only be converted from/to local date/times at point of interaction with a person.

As we can't see how you have actually done the query and how you are presenting it, it isn't possible to give exact help here. In general, if you have a timestamp in UTC and wish to convert to local, you can use MomentJS which is built into JSONata (e.g. via the change node), there is also a somewhat dated but still working custom node that I wrote. Or, if you are happy with JavaScript, you can use a function node with Node.js's built-in Intl - JavaScript library library.

3

What I do to store the datetime is I generate first

msg.payload = new Date();
return msg;

Then I pass this to moment node to adjust to correct timezone and after that have function node to insert it to sql table with other data like normal

4

Thanks for the advice, but what I just want is display whatever data inside my table to dashboard, but the issue now is why it converts the data back into UTC for nodered. I need some reference for the sql query where I can keep the original data from sql to nodered

5

Not a good idea, better to stay with UTC and reformat to a valid SQL date/time stamp. Then there can be no confusion. But, of course, your choice.

The reason it appears that way is because something has converted it to a JavaScript Date object and then you've output that to debug I suspect. That last part has to convert the object to a string and JavaScript's default for that is to use ISO8601 format. That format is ALWAYS in UTC (strictly "Zulu" hence the Z at the end which is the same thing UTC=Zulu=GMT, go figure!).

6

Ok I see now. If I let it stay in UTC format, how I reformat it to valid SQL datetime?
And then, if I pass normal select query to display on dashboard, will it convert to UTC format again? If yes, no point of doing that bcs my goal is to display user the local time format.

7

https://intellipaat.com/blog/tutorial/sql-tutorial/sql-formatting/

8

As mentioned before, if you allow JavaScript to convert something to a JSON date (e.g. if it started as a JavaScript Date object), you will always get output in UTC because that's what the JSON.stringify function does. If you find that happening, simply convert the data to a formatted string before sending it to the Dashboard.

When node-red sends data to Dashboard, it uses a library called Socket.IO and websockets. By default, websockets only handles string data. So if you try to send a JavaScript object other than a string, it will generally be converted. The Socket.IO library will try to convert back at the front end but the act of going from an object to a JSON string and back is not perfectly reversible. Date objects being one of the things that are impacted.

9

What I did is just something like this
image
And get output from debug node
image

10

OK, so you are actually getting ISO8602 strings from whatever node you are using to do the query. Slightly different to what I said but quite possibly the same issue but is either what your DB engine is returning or is what the custom node is returning.

Short of other choices, you should use your DB engine's SQL engine to format the returned dates as mentioned by smanjunath211 above.

You've not mentioned which SQL DB or node you are using. Not that I could be more specific myself because I very rarely use SQL these days. But others may well be able to help further.

11

Ok nvm. I found the way. Refer to this site

so my payload becomes

msg.payload = "Select convert (varchar, start_date, 20) AS [Start Date] from dbo.production";
return msg;
12

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