SQL Dashboard Bar Chart troubleshoot

Dashboard
, , ,
1

Hello dear community,

this is my first post in this forum, so please don't be too strict.

In my module IOT Smart Devices at the university I have to write data into a database with the help of a mqtt broker, which is collected with a smart socket.

This is actually only background knowledge and not too important I think.
My task is now to commutate the power data that the socket reads out and sends to the SQL database with a timestamp and to calculate a daily consumption and to create a bar chart with the daily consumption of the last 7 days from the irregular entries. Unfortunately, the smart socket has been switched off in the meantime, which is why we have large gaps in the data. The socket is now transmitting data to the SQL database again.

ChatGPT has helped me so far, but after 2 days of troubleshooting I'm running out of steam and am now trying to get help this way! Please help me to display the bar chart.

My flow consists of an Inject -> Function ( SQL query) -> My SQL (database) -> Function (data preparation) -> ui_chart (daily consumption of the last 7 days "bar chart")

Screenshot 2024-07-09 223228
Screenshot 2024-07-09 223228302×1028 23.6 KB

Screenshot 2024-07-09 223413
Screenshot 2024-07-09 223413794×273 36.4 KB

function for SQL Query:
msg.topic = `SELECT DATE(timestamp) as Date, SUM(Power) as DailyPower
FROM Shelly_Data
WHERE timestamp >= NOW() - INTERVAL 7 DAY
GROUP BY DATE(timestamp)
ORDER BY DATE(timestamp) ASC`;
return msg;

function for data formatting:

irgnore the comments if you are not German. The plan is to get a value for every day of the last week. If there is no day with values, it should get the value zero.

// Funktion zur Formatierung der Daten für den ui_chart
let data = msg.payload;

// Debug-Ausgabe der Rohdaten
node.warn("Rohdaten: " + JSON.stringify(data));

// Erstellen eines Arrays für die letzten 7 Tage ab heute
let today = new Date();
let labels = [];
let seriesData = [];

// Füllen der Labels für die letzten 7 Tage
for (let i = -6; i <= 0; i++) {
    let date = new Date(today);
    date.setDate(date.getDate() + i);
    labels.push(date.toISOString().split('T')[0]); // Format: YYYY-MM-DD
    seriesData.push(0); // Initialisieren mit 0
}

// Zuordnen der tatsächlichen Daten zu den Labels
if (Array.isArray(data) && data.length > 0) {
    data.forEach(item => {
        let date = new Date(item.Date).toISOString().split('T')[0];
        let index = labels.indexOf(date);
        if (index > -1) {
            seriesData[index] = parseFloat(item.DailyPower); // Sicherstellen, dass es eine Zahl ist
        }
    });
}

// Formatieren des Payloads für den ui_chart
msg.payload = {
    labels: labels,
    series: [seriesData]
};

// Debug-Ausgabe des formatierten Payloads
node.warn("Formatierter Payload: " + JSON.stringify(msg.payload));

// Rückgabe des msg Objekts
return msg;
2

The data should be in an object property named data. The whole object should be in an array
e.g.

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

[edit] confusion are we talking node-red-dashboard or DB2, as you have tagged both.

3

One possible approach to get a zero total for the missing days is to have a calendar table with one record for every day.
There is an example of populating such a table at https://www.brianshowalter.com/calendar_tables, though his example has lots of fields you don't need.
All you need is for every day the first field dt eg "2024-07-09"

Then your SQL query becomes something like

SELECT ct.dt, DATE(sd.timestamp) as Date, SUM(sd.Power) as DailyPower
FROM calendar_table ct LEFT OUTER JOIN Shelly_Data sd
ON DATE(sd.timestamp) = ct.dt
WHERE sd.timestamp >= NOW() - INTERVAL 7 DAY
GROUP BY DATE(sd.timestamp)
ORDER BY DATE(sd.timestamp) ASC`

And you will (hopefully) get records with DailyPower 0 for the missing days.
(I'm not sure about the WHERE clause, perhaps it's more efficient to restrict by ct.dt, depends how smart the query optimiser is)

Edit : you should group and order by ct.dt because sd.timestamp might be null.

4

This is the Node Red Dashboard. I have now corrected the tags for this post accordingly.

5

Thank you for your feedback. I will follow this approach and hope that it will work.

6

Looking at it again in the morning...

Since the whole idea of the LEFT OUTER JOIN is to construct records where Shelly_Data.timestamp is null, you definitely need to change the WHERE, GROUP BY and ORDER BY clauses to use calendar_table.dt.

So perhaps this will work. Check the syntax for your variety of SQL.

SELECT ct.dt as Date, DATE(sd.timestamp) as SDDate, SUM(sd.Power) as DailyPower
FROM calendar_table ct LEFT OUTER JOIN Shelly_Data sd
ON DATE(sd.timestamp) = ct.dt
WHERE ct.dt >= NOW() - INTERVAL 7 DAY
GROUP BY ct.dt
ORDER BY ct.dt ASC