# Wrong Timezone data in sql query result?!?

**URL:** <https://discourse.nodered.org/t/wrong-timezone-data-in-sql-query-result/60658>\
**Category:** General\
**Created:** [1 April 2022 19:38 UTC](https://discourse.nodered.org/t/wrong-timezone-data-in-sql-query-result/60658 "2022-04-01T19:38:52Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![markush](https://avatars.discourse-cdn.com/v4/letter/m/58956e/32.png) [@markush](https://discourse.nodered.org/u/markush)\
**Post date:** [1 April 2022 19:38 UTC](https://discourse.nodered.org/t/wrong-timezone-data-in-sql-query-result/60658/1 "2022-04-01T19:38:52Z")

</div>

Hello,

i try to show some data of my postgresql database in node-red via the table node. The query in pg-admin show the following result sets:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/b/9b3d99338b44fd4b8ca7ecd11641a69518d5c2c1.png)

The same query results the following data in node-red:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/4/d405e90067a6c9fdeb1afcebfea06967f1fa3622.png)

The "tag" (day in english) shows the wrong value!

The query:

```auto
-- Tagesverbrauch ermitteln
with sub as (
select distinct(s_timestamp::date) as tag
from smarthome.strom
	),
sub2 as (
	--select * from sub
	select sub.*, max(s_value)
	from smarthome.strom, sub
	where s_timestamp::date = sub.tag
	group by sub.tag
	)
select *, sub2.max - lag(sub2.max) over (order by tag) as "Tagesverbrauch" from sub2
order by tag desc

```

How can i fix this?

A "select current\_timestamp" shows the correct value in pg-admin and in node-red!??

Thanx!

---

<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:** [1 April 2022 19:53 UTC](https://discourse.nodered.org/t/wrong-timezone-data-in-sql-query-result/60658/2 "2022-04-01T19:53:07Z")

</div>

It would help if you shared with us the tz of the server (or servers if the db and node-red are on different ones). It would also help if you shared the full timestamp from the db rather than just the date.

At a guess, I'd say that the data in the db has been (wrongly) stored in a local tz format. The output from node-red is being shown in UTC.

You should pretty much always store db entries in UTC to avoid these kinds of issues and many more.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [1 April 2022 19:55 UTC](https://discourse.nodered.org/t/wrong-timezone-data-in-sql-query-result/60658/3 "2022-04-01T19:55:31Z")

</div>

My guess is your timezone is GMT+02:00

If so, then there is nothing wrong with the timestamp. It is a correct UTC representation of the actual time (expressed in UTC)

Example, I am in GMT+1  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/5/b51f780729eb24af080568aa5a6b01988e0f0102.png)

The question becomes, what is the `typeof` msg.payload[0].tag? Is is a string or a `Date` object?

Can you add

```auto
node.warn( ["msg.payload[0].tag", msg.payload[0].tag, typeof msg.payload[0].tag] )

```

in a function node AFTER the query - so we can see what the database node is actually giving you?

---

<div class="post-metadata">

**Author:** ![markush](https://avatars.discourse-cdn.com/v4/letter/m/58956e/32.png) [@markush](https://discourse.nodered.org/u/markush)\
**Post date:** [1 April 2022 20:03 UTC](https://discourse.nodered.org/t/wrong-timezone-data-in-sql-query-result/60658/4 "2022-04-01T20:03:26Z")

</div>

It's a timestamp without TZ:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/b/4b573117f5ba0ba9eb3642ca62ee85a01d0ade4c.png)

The db and node-red on the same server:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/4/b4479bb645da34b6bc143587c1c1897e392fb38b.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:** [1 April 2022 20:05 UTC](https://discourse.nodered.org/t/wrong-timezone-data-in-sql-query-result/60658/5 "2022-04-01T20:05:46Z")

</div>

That is certainly the worst possible approach I'm afraid. And actually, I'm not entirely sure it is true but I don't really know Postgres so I couldn't say offhand.

The problem is that you now have no idea what happens when DST starts and stops. So even though you could "fix" the immediate problem, it probably wouldn't solve all of your issues.

---

<div class="post-metadata">

**Author:** ![markush](https://avatars.discourse-cdn.com/v4/letter/m/58956e/32.png) [@markush](https://discourse.nodered.org/u/markush)\
**Post date:** [1 April 2022 20:07 UTC](https://discourse.nodered.org/t/wrong-timezone-data-in-sql-query-result/60658/6 "2022-04-01T20:07:28Z")

</div>

I get the following output:

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/3/c3057911fa53697aaeacc62f78d6009e30cc29da.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:** [1 April 2022 20:10 UTC](https://discourse.nodered.org/t/wrong-timezone-data-in-sql-query-result/60658/7 "2022-04-01T20:10:06Z")

</div>

It is a string which is to be expected since that is how JSON formats a JavaScript Date object (ISO8601 format which is always UTC). Not really the issue though I don't believe.

The issue is that it isn't clear what TZ the table data is stored in nor is it clear what happens during changeover of DST.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [1 April 2022 20:12 UTC](https://discourse.nodered.org/t/wrong-timezone-data-in-sql-query-result/60658/8 "2022-04-01T20:12:28Z")

</div>

Julian, I suspect the OP is storing date only. but the node-red query is returning a date object from the query.

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/1/a15af1ee072caeea8ca4401883b193161a02aaeb.png)

Therefore, with a value of "2022-04-01", the node-red contrib node is returning a date object with a value of "2022-03-01 22:00:00.000Z" which is actually 2022-04-01 00:00:00" in Berlin.

i.e. there is not a problem. Only that the op is viewing a UTC version of the date.

The question becomes what are you wanting to do with this value? It is a correct UTC representation of the value stored.

---

<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:** [1 April 2022 20:13 UTC](https://discourse.nodered.org/t/wrong-timezone-data-in-sql-query-result/60658/9 "2022-04-01T20:13:24Z")

</div>

> [@Steve-Mcl](#):
>
> I suspect the OP is storing date only. but the node-red query is returning a date object from the query.

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/0/00a96cd3ac7a3cae199bd6d5a42fef17ca00a708.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [1 April 2022 20:14 UTC](https://discourse.nodered.org/t/wrong-timezone-data-in-sql-query-result/60658/10 "2022-04-01T20:14:42Z")

</div>

So it looks like the tag is an object (most likely a Date)

What do you get if you change that function code to this...

```auto
node.warn( ["msg.payload[0].tag", msg.payload[0].tag, msg.payload[0].tag.toLocaleString()] )

```

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [1 April 2022 20:15 UTC](https://discourse.nodered.org/t/wrong-timezone-data-in-sql-query-result/60658/11 "2022-04-01T20:15:18Z")

</div>

> [@TotallyInformation](#):
>
> ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/0/00a96cd3ac7a3cae199bd6d5a42fef17ca00a708.png)

That could very well be how "that application" represents a UTC value.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [1 April 2022 20:18 UTC](https://discourse.nodered.org/t/wrong-timezone-data-in-sql-query-result/60658/12 "2022-04-01T20:18:20Z")

</div>

@TotallyInformation I'm getting a bit lost off now. I read (in the first post) "Date values" are being stored but the node is returning a value 2hrs earlier.

We've established the `tag` value is a date object so I've assumed the OP is concerned the value returned from the query was 2hrs earlier. But in fact it is the UTC representation of the time stored (i.e. it is correct!)

@markush Can you clarify what you are writing to the DB and what you expect to get back?

---

<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:** [1 April 2022 20:52 UTC](https://discourse.nodered.org/t/wrong-timezone-data-in-sql-query-result/60658/13 "2022-04-01T20:52:56Z")

</div>

> [@Steve-Mcl](#):
>
> I'm getting a bit lost off now

Indeed. The DB is clearly storing a timestamp not a date. It is simply being shown as a Date in the first output shared but some values appear as the wrong date because of the timezone difference.

The representation in Node-RED is confusing because the output has been further translated to ISO8601 due to JSON transcription. ISO8601 is always a UTC representation.

These are some of the reasons I keep repeating that you should only ever store UTC timestamps in a DB. It avoids all of this confusion and prevents transcription errors due to timezones and DST changes.

Get user input in local time and give user output in local time but always store and calculate in UTC.

---

<div class="post-metadata">

**Author:** ![markush](https://avatars.discourse-cdn.com/v4/letter/m/58956e/32.png) [@markush](https://discourse.nodered.org/u/markush)\
**Post date:** [2 April 2022 09:39 UTC](https://discourse.nodered.org/t/wrong-timezone-data-in-sql-query-result/60658/14 "2022-04-02T09:39:35Z")

</div>

> [@TotallyInformation](#):
>
> Get user input in local time and give user output in local time but always store and calculate in UTC.

This is probably the best way! I have now changed the data property of the column:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/f/bf6b1d319e050ba63cf76edb35d65d04872ec263.png)

For the correct display i "change" the value with rewriting the date value:

```auto
//node.warn( ["msg.payload[0].tag", msg.payload[0].tag, typeof msg.payload[0].tag] )
var load = msg.payload;
var consumpData = [];
var obj;

var options = { year: 'numeric', month: 'numeric', day: 'numeric' };

for (let i = load.length - 1; i >= 0; i--){
    obj = {};
    obj.Tag = load[i].tag.toLocaleDateString('de-DE', options);
    obj.Tagesverbrauch = load[i].Tagesverbrauch;
    consumpData.push(obj);
}

msg.payload = consumpData;

return msg;

```

Now everything looks fine 🙂 👍  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/2/126c4431e33aa13bbf4b6458113bb47bdb92ded1.png)

Thank you for your support!

---

<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:** [16 April 2022 09:39 UTC](https://discourse.nodered.org/t/wrong-timezone-data-in-sql-query-result/60658/15 "2022-04-16T09:39:37Z")

</div>

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