Wrong Timezone data in sql query result?!?

General
1

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

The same query results the following data in node-red:
image

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

The query:

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

2

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.

3

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

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

Can you add

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?

4

It's a timestamp without TZ:
image

The db and node-red on the same server:
image

5

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.

6

I get the following output:

image

7

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.

8

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

image

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.

9

image

10

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

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

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

12

@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?

13

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.

14

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

image
image708×116 9.56 KB

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

//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 :slightly_smiling_face: :+1:
image

Thank you for your support!

15

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