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

**URL:** <https://discourse.nodered.org/t/sql-query-of-datetime-column-returning-utc-time-not-the-table-data/71814>\
**Category:** General\
**Created:** [7 December 2022 10:04 UTC](https://discourse.nodered.org/t/sql-query-of-datetime-column-returning-utc-time-not-the-table-data/71814 "2022-12-07T10:04:30Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![Kimo](https://avatars.discourse-cdn.com/v4/letter/k/848f3c/32.png) [@Kimo](https://discourse.nodered.org/u/Kimo)\
**Post date:** [7 December 2022 10:04 UTC](https://discourse.nodered.org/t/sql-query-of-datetime-column-returning-utc-time-not-the-table-data/71814/1 "2022-12-07T10:04:30Z")

</div>

Hi,  
I got an issue where I store a timestamp into a datetime table in sql and get result like this  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/8/e8494e6e533bd622a24a45718adda5dbf4cd4612.png)

But then the problem is when I select query this data into nodered ui, it return the data in UTC format  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/d/7d058600c4e203802c381b530a852f0fbd33a5f2.png)

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

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [7 December 2022 10:55 UTC](https://discourse.nodered.org/t/sql-query-of-datetime-column-returning-utc-time-not-the-table-data/71814/2 "2022-12-07T10:55:32Z")

</div>

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](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Intl) library.

---

<div class="post-metadata">

**Author:** ![Kimo](https://avatars.discourse-cdn.com/v4/letter/k/848f3c/32.png) [@Kimo](https://discourse.nodered.org/u/Kimo)\
**Post date:** [7 December 2022 11:10 UTC](https://discourse.nodered.org/t/sql-query-of-datetime-column-returning-utc-time-not-the-table-data/71814/3 "2022-12-07T11:10:40Z")

</div>

What I do to store the datetime is I generate first

```auto
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

---

<div class="post-metadata">

**Author:** ![Kimo](https://avatars.discourse-cdn.com/v4/letter/k/848f3c/32.png) [@Kimo](https://discourse.nodered.org/u/Kimo)\
**Post date:** [7 December 2022 11:12 UTC](https://discourse.nodered.org/t/sql-query-of-datetime-column-returning-utc-time-not-the-table-data/71814/4 "2022-12-07T11:12:50Z")

</div>

> [@TotallyInformation](#):
>
> MomentJS which is built into JSONata

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

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [7 December 2022 11:16 UTC](https://discourse.nodered.org/t/sql-query-of-datetime-column-returning-utc-time-not-the-table-data/71814/5 "2022-12-07T11:16:45Z")

</div>

> [@Kimo](#):
>
> What I do to store the datetime is I generate first

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.

> [@Kimo](#):
>
> why it converts the data back into UTC for nodered.

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!).

---

<div class="post-metadata">

**Author:** ![Kimo](https://avatars.discourse-cdn.com/v4/letter/k/848f3c/32.png) [@Kimo](https://discourse.nodered.org/u/Kimo)\
**Post date:** [8 December 2022 01:45 UTC](https://discourse.nodered.org/t/sql-query-of-datetime-column-returning-utc-time-not-the-table-data/71814/6 "2022-12-08T01:45:13Z")

</div>

> [@TotallyInformation](#):
>
> better to stay with UTC and reformat to a valid SQL date/time stamp

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.

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [8 December 2022 02:03 UTC](https://discourse.nodered.org/t/sql-query-of-datetime-column-returning-utc-time-not-the-table-data/71814/7 "2022-12-08T02:03:08Z")

</div>

[https://intellipaat.com/blog/tutorial/sql-tutorial/sql-formatting/](https://intellipaat.com/blog/tutorial/sql-tutorial/sql-formatting/)

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [8 December 2022 09:30 UTC](https://discourse.nodered.org/t/sql-query-of-datetime-column-returning-utc-time-not-the-table-data/71814/8 "2022-12-08T09:30:59Z")

</div>

> [@Kimo](#):
>
> if I pass normal select query to display on dashboard, will it convert to UTC format again?

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](http://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](http://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.

---

<div class="post-metadata">

**Author:** ![Kimo](https://avatars.discourse-cdn.com/v4/letter/k/848f3c/32.png) [@Kimo](https://discourse.nodered.org/u/Kimo)\
**Post date:** [8 December 2022 16:07 UTC](https://discourse.nodered.org/t/sql-query-of-datetime-column-returning-utc-time-not-the-table-data/71814/9 "2022-12-08T16:07:17Z")

</div>

> [@TotallyInformation](#):
>
> 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.

What I did is just something like this  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/a/8a992fb12d898cd48bf7de708d49fe8090bd6248.png)  
And get output from debug node  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/7/e7bc0fff2318e5e4fe467b50a6b52fc606f2e211.png)

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [8 December 2022 17:02 UTC](https://discourse.nodered.org/t/sql-query-of-datetime-column-returning-utc-time-not-the-table-data/71814/10 "2022-12-08T17:02:57Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Kimo](https://avatars.discourse-cdn.com/v4/letter/k/848f3c/32.png) [@Kimo](https://discourse.nodered.org/u/Kimo)\
**Post date:** [9 December 2022 01:00 UTC](https://discourse.nodered.org/t/sql-query-of-datetime-column-returning-utc-time-not-the-table-data/71814/11 "2022-12-09T01:00:49Z")

</div>

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

> **[Date and Time Conversions Using SQL Server](https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/)**
>
> In this article we look at several different date and time formats you can use in SQL Server.

so my payload becomes

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

```

---

<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:** [23 December 2022 01:01 UTC](https://discourse.nodered.org/t/sql-query-of-datetime-column-returning-utc-time-not-the-table-data/71814/12 "2022-12-23T01:01:33Z")

</div>

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