Help with Java: timestamps result to Jan 20th 1970

I despair of this error.

A function node receives values from a database, edits these, and sends them to a chart.
There are two types of data:

  • sum_of_minutes --> the x-axis shows values in HH:MM
  • sum_of_days --> the x-axis shall show the day in month (.getDate() )

As long as this function receives "sum_of_minutes", everything is fine. The x-axis of the chart shows times in HH:MM.
But when it comes to "sum_of_days" this thing becomes weird to me since I am not able to find or even UNDERSTAND the bug.

the msg.payload from the database for "sum_of_days" is:

[{"TIME":"1696204799","DAYSUM":0.8},{"TIME":"1696291199","DAYSUM":0.98},{"TIME":"1696377599","DAYSUM":0.36},{"TIME":"1696463999","DAYSUM":0.9},{"TIME":"1696550399","DAYSUM":0.65},{"TIME":"1696636799","DAYSUM":0.96},{"TIME":"1696723199","DAYSUM":0.96},{"TIME":"1696809599","DAYSUM":0.94},{"TIME":"1696895999","DAYSUM":0.73},{"TIME":"1696982399","DAYSUM":0.8},{"TIME":"1697068799","DAYSUM":0.8429575},{"TIME":"1697155199","DAYSUM":0.49678083333333345},{"TIME":"1697241599","DAYSUM":0.4944325000000001},{"TIME":"1697327999","DAYSUM":0.6986259999999999}]

As you can see: "TIME" holds different values (Unix- timestamps) for each field.

The result of the function is this:

{"series":[],"data":[[0.8,0.98,0.36,0.9,0.65,0.96,0.96,0.94,0.73,0.8,0.8429575,0.49678083333333345,0.4944325000000001,0.6986259999999999]],"labels":[20,20,20,20,20,20,20,20,20,20,20,20,20,20]}

As you can see, "labels" holds the same value (20) for each field.

Here is the code of that function:

var data = [];
var times = [];
var arrayLength;
var i;
var dt;
var x_axis = "";
var dummy = [];

if (msg.type_of_data == "sum_of_minutes") {
    msg.payload.forEach(function (value) {
        data.push(value['MINUTESUM']);
        times.push(value['TIME']);
    });
}

if (msg.type_of_data == "sum_of_days") {
    msg.payload.forEach(function (value) {
        data.push(value['DAYSUM']);
        times.push(value['TIME']);
    });
}

arrayLength = times.length;

for (i = 0; i < arrayLength; i++) {
    dt = new Date(Number(times[i]));

    if (msg.type_of_data == "sum_of_minutes") {
        x_axis = `${dt.getHours().toString().padStart(2, "0")}:${dt.getMinutes().toString().padStart(2, "0")}`;
    }
    if (msg.type_of_data == "sum_of_days") {
        x_axis = dt.getDate();
        dummy[i] = times[i] + " ; " + x_axis + " ; " + dt;
    }

    times[i] = x_axis;
};

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

msg.timedata = dummy;

return msg;

Please have a look at this line in the FOR-Loop where I tried some debugging:

        dummy[i] = times[i] + " ; " + x_axis + " ; " + dt;

the result of that command is msg.timedata and looks like this:

["1696204799 ; 20 ; Tue Jan 20 1970 16:10:04 GMT+0100 (Mitteleuropäische Normalzeit)","1696291199 ; 20 ; Tue Jan 20 1970 16:11:31 GMT+0100 (Mitteleuropäische Normalzeit)","1696377599 ; 20 ; Tue Jan 20 1970 16:12:57 GMT+0100 (Mitteleuropäische Normalzeit)","1696463999 ; 20 ; Tue Jan 20 1970 16:14:23 GMT+0100 (Mitteleuropäische Normalzeit)","1696550399 ; 20 ; Tue Jan 20 1970 16:15:50 GMT+0100 (Mitteleuropäische Normalzeit)","1696636799 ; 20 ; Tue Jan 20 1970 16:17:16 GMT+0100 (Mitteleuropäische Normalzeit)","1696723199 ; 20 ; Tue Jan 20 1970 16:18:43 GMT+0100 (Mitteleuropäische Normalzeit)","1696809599 ; 20 ; Tue Jan 20 1970 16:20:09 GMT+0100 (Mitteleuropäische Normalzeit)","1696895999 ; 20 ; Tue Jan 20 1970 16:21:35 GMT+0100 (Mitteleuropäische Normalzeit)","1696982399 ; 20 ; Tue Jan 20 1970 16:23:02 GMT+0100 (Mitteleuropäische Normalzeit)","1697068799 ; 20 ; Tue Jan 20 1970 16:24:28 GMT+0100 (Mitteleuropäische Normalzeit)","1697155199 ; 20 ; Tue Jan 20 1970 16:25:55 GMT+0100 (Mitteleuropäische Normalzeit)","1697241599 ; 20 ; Tue Jan 20 1970 16:27:21 GMT+0100 (Mitteleuropäische Normalzeit)","1697327999 ; 20 ; Tue Jan 20 1970 16:28:47 GMT+0100 (Mitteleuropäische Normalzeit)"]

So ... it appears to me that the problem is that:

    dt = new Date(Number(times[i]));

since it calculates for each timestamp the same day in 1970 but with different times.
But I have no clue why that happens and why this does not work for "sum_of_days" but for "sum_of_minutes".

Please ... can anyone switch the light on?

Have a great weekend!

Can you show us the structure of the database table (describe tablename) and the SQL query that retrieves the data?

This is a continuation of this topic

Your time stamps are different, the first topic they are unix milliseconds timestamps, and in this topic they are unix seconds timestamps.

Either correct the time stamps and use milliseconds in both database tables, or multiple these new timestamps by 1000.

e.g

for (i = 0; i < arrayLength; i++) {

    if (msg.type_of_data == "sum_of_minutes") {
        dt = new Date(Number(times[i]));
        x_axis = `${dt.getHours().toString().padStart(2, "0")}:${dt.getMinutes().toString().padStart(2, "0")}`;
    }
    else if (msg.type_of_data == "sum_of_days") {
        dt = new Date(Number(times[i])*1000);
        x_axis = dt.getDate();
        dummy[i] =  x_axis;
    }

    times[i] = x_axis;
};
2 Likes

Thanks again for your help. Haven't seen that the timestamps are different.

Once you fix timestamps in DB, this may be a simpler way using timeseries data object and sending msg.uicontrol to adjust the x axis format.

[{"id":"dd6e2667e2c98339","type":"inject","z":"d1395164b4eec73e","name":"minute sum","props":[{"p":"payload"},{"p":"type_of_data","v":"MINUTESUM","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"TIME\":1696831539163,\"MINUTESUM\":0},{\"TIME\":1696831601046,\"MINUTESUM\":2.58},{\"TIME\":1696831659189,\"MINUTESUM\":2.6},{\"TIME\":1696831719379,\"MINUTESUM\":2.6},{\"TIME\":1696831781108,\"MINUTESUM\":2.83},{\"TIME\":1696831839203,\"MINUTESUM\":3.36},{\"TIME\":1696831899218,\"MINUTESUM\":3.57},{\"TIME\":1696831959105,\"MINUTESUM\":4.26},{\"TIME\":1696832021111,\"MINUTESUM\":4.42},{\"TIME\":1696832079160,\"MINUTESUM\":4.58},{\"TIME\":1696832139102,\"MINUTESUM\":4.8},{\"TIME\":1696832199151,\"MINUTESUM\":4.94},{\"TIME\":1696832259215,\"MINUTESUM\":5.18},{\"TIME\":1696832319103,\"MINUTESUM\":5.82},{\"TIME\":1696832379210,\"MINUTESUM\":6.27},{\"TIME\":1696832439201,\"MINUTESUM\":6.5},{\"TIME\":1696832499225,\"MINUTESUM\":7.26},{\"TIME\":1696832559245,\"MINUTESUM\":6.95},{\"TIME\":1696832619184,\"MINUTESUM\":7.14},{\"TIME\":1696832679394,\"MINUTESUM\":7.83},{\"TIME\":1696832739283,\"MINUTESUM\":7.77},{\"TIME\":1696832799188,\"MINUTESUM\":8.82},{\"TIME\":1696832859187,\"MINUTESUM\":10.42},{\"TIME\":1696832919285,\"MINUTESUM\":9.93},{\"TIME\":1696832979228,\"MINUTESUM\":9.63},{\"TIME\":1696833039114,\"MINUTESUM\":10.76},{\"TIME\":1696833099187,\"MINUTESUM\":9.56},{\"TIME\":1696833159178,\"MINUTESUM\":10.1},{\"TIME\":1696833219310,\"MINUTESUM\":9.69}]","payloadType":"json","x":90,"y":1180,"wires":[["fe0adff4137a185c"]]},{"id":"fe0adff4137a185c","type":"function","z":"d1395164b4eec73e","name":"function 64","func":"const data = []\n\nconst lookup = {\n    \"MINUTESUM\": \"HH:mm\",\n    \"DAYSUM\": \"DD\"\n}\n// send uicontrol message to adjust x format\nnode.send({ui_control: {xformat: lookup[msg.type_of_data]}});\n\n//create time series chart data\nmsg.payload.forEach(function(value) {\n    data.push({\n        y: value[msg.type_of_data], // adds value.MINUTESUM or value.DAYSUM\n        x: value.TIME\n    })\n});\n\nmsg.payload = [{\n    series: [msg.type_of_data],\n    data: [data],\n}];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":290,"y":1200,"wires":[["148794c383ef62d7"]]},{"id":"2afd88b375d3a9cd","type":"inject","z":"d1395164b4eec73e","name":"day sum","props":[{"p":"payload"},{"p":"type_of_data","v":"DAYSUM","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"TIME\":1696831539163,\"DAYSUM\":0},{\"TIME\":1696831601046,\"DAYSUM\":2.58},{\"TIME\":1696831659189,\"DAYSUM\":2.6},{\"TIME\":1696831719379,\"DAYSUM\":2.6},{\"TIME\":1696831781108,\"DAYSUM\":2.83},{\"TIME\":1696831839203,\"DAYSUM\":3.36},{\"TIME\":1696831899218,\"DAYSUM\":3.57},{\"TIME\":1696831959105,\"DAYSUM\":4.26},{\"TIME\":1696832021111,\"DAYSUM\":4.42},{\"TIME\":1696832079160,\"DAYSUM\":4.58},{\"TIME\":1696832139102,\"DAYSUM\":4.8},{\"TIME\":1696832199151,\"DAYSUM\":4.94},{\"TIME\":1696832259215,\"DAYSUM\":5.18},{\"TIME\":1696832319103,\"DAYSUM\":5.82},{\"TIME\":1696832379210,\"DAYSUM\":6.27},{\"TIME\":1696832439201,\"DAYSUM\":6.5},{\"TIME\":1696832499225,\"DAYSUM\":7.26},{\"TIME\":1696832559245,\"DAYSUM\":6.95},{\"TIME\":1696832619184,\"DAYSUM\":7.14},{\"TIME\":1696832679394,\"DAYSUM\":7.83},{\"TIME\":1696832739283,\"DAYSUM\":7.77},{\"TIME\":1696832799188,\"DAYSUM\":8.82},{\"TIME\":1696832859187,\"DAYSUM\":10.42},{\"TIME\":1696832919285,\"DAYSUM\":9.93},{\"TIME\":1696832979228,\"DAYSUM\":9.63},{\"TIME\":1696833039114,\"DAYSUM\":10.76},{\"TIME\":1696833099187,\"DAYSUM\":9.56},{\"TIME\":1696833159178,\"DAYSUM\":10.1},{\"TIME\":1696833219310,\"DAYSUM\":9.69}]","payloadType":"json","x":100,"y":1240,"wires":[["fe0adff4137a185c"]]},{"id":"148794c383ef62d7","type":"ui_chart","z":"d1395164b4eec73e","name":"","group":"8b5cde76.edd58","order":8,"width":0,"height":0,"label":"chart","chartType":"line","legend":"false","xformat":"HH:mm","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":450,"y":1200,"wires":[["57e8d5adc2c49bad"]]},{"id":"57e8d5adc2c49bad","type":"debug","z":"d1395164b4eec73e","name":"debug 344","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":590,"y":1200,"wires":[]},{"id":"8b5cde76.edd58","type":"ui_group","name":"default","tab":"8f03e639.85956","order":1,"disp":false,"width":"12","collapse":false},{"id":"8f03e639.85956","type":"ui_tab","name":"Home","icon":"dashboard","order":3,"disabled":false,"hidden":false}]
const data = []

const lookup = {
    "MINUTESUM": "HH:mm",
    "DAYSUM": "DD"
}
// send uicontrol message to adjust x format
node.send({ui_control: {xformat: lookup[msg.type_of_data]}});

//create time series chart data
msg.payload.forEach(function(value) {
    data.push({
        y: value[msg.type_of_data], // adds value.MINUTESUM or value.DAYSUM
        x: value.TIME
    })
});

msg.payload = [{
    series: [msg.type_of_data],
    data: [data],
}];
return msg;


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