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:
The same query results the following data in node-red:
The "tag" (day in english) shows the wrong value!
-- Tagesverbrauch ermitteln
with sub as (
select distinct(s_timestamp::date) as tag
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!??
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.
@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?
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.