Strange Data from postgres node


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:

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:

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.


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

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:

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

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

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?

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

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.

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

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


The object for this data:


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

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.

Okay, thats interesting!

Thank your for you help! :+1:

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

Object from the database:

Object for the ui_chart:

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(labels +' ' + data);

    "series": [],
    "data": [data],
    "labels": labels

return msg;

Maybe it will be helpful for someone!

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