Strange Data from postgres node

General
1

Hello,

i'm trying to implement a bar display of some data stored in a postgres database. With a query executed in Postbird Tool i get the following output:
image

The query in node-red with a function node and a postgres node looks like this:

msg.payload = 'select distinct(event_date) as label, max(energytoday) as payload from smarthome.pv group by event_date';
return msg;

The returned data looks like this:
image

The date fields in the label column wrong!? There exists no date "2022-02-24" in the database! But why is it so? event_date is a "normal" date field in the database.

Greets

2

The dates returned by your sql query are Zulu time.
What time zone are you in?
How did you insert the data?

3

My time zone is Europe\Berlin. Insert query uses the 'current_date' function for storying the date field.

Postbird shows the stored data with correct date field:

image
image996×133 8.16 KB

4

Maybe postbird is displaying the times in your local time? That would show the first record from your query as 22-02-25 00:00

5

Yes, that would be possible...
In the end: i have to convert that values to my local timezone!?!?

6

That depends on what you want to do with it. The date/time it is giving you is midnight local time, which I presume it's what you want. What you going to do with it?

7

I want to show a bar graph at the dasboard. Therefore i need the local time. Found a possible solution to convert it:

console.log(event.toLocaleString('en-GB', { timeZone: 'UTC' }));

But every record must be converted...

8

If you are using the dashboard chart node it will expect the timestamps in UTC which is what you have. The chart node will display it in local time.

9

Thank you for this information! I will try it with the chart node.

10

Sure it shows using the local time settings? I tried and get the following display:

image

The object for this data:

image

Is there a special way for the chart node to show the correct Date?

11

Sorry, I missed that you were using a bar chart. My comment was about the line chart.

For bar chart labels you will have to format the label yourself.

12

Okay, thats interesting!

Thank your for you help! :+1:

13

Here is my solution to show data from a postgresql database in a bar chart:

Object from the database:

image
image247×588 5.17 KB

Object for the ui_chart:
image

Here is the code for the function node to convert the data from one object to the other:

var load = msg.payload;
var labels = [];
var data = [];

for(let i = load.length - 1; i >= 0; i--) {
    //node.warn(load[i].event_date);
    labels.push((load[i].event_date.toLocaleDateString('de-DE')));
    data.push(load[i].energytoday);
}
//node.warn(labels +' ' + data);

msg.payload=[{
    "series": [],
    "data": [data],
    "labels": labels
}];

return msg;

Maybe it will be helpful for someone!

14

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