Timestamp in function problems

Hi,

I'm creating a data collection system for work that involves reading data from PLCs and PCs and put it on the dashboard, as well as into a centralised database.

We have several "smart" storage units that store material racks. These are using an SQL express database to store the information on each rack, including the type of material, and the time in which the rack was inserted in the storage unit shelf. I get this as an array of objects, which later I process in a function to get an HTML string for a table and display through a text node.

The output table looks like this:

In the object I get from the supplier, they offer me a timestamp that says when the material was placed in the storage. For some reason, the supplier used a non-standard format for the timestamps, which basically comes down to a string of numbers (no spaces, dashes, or any other characters) as in "yyyymmddhhmmss". In order to fix the timestamp and convert it into ISO format, I split the string into each part, parse it into an integer value, and create a date object from there. This is the easy part, and it works.

Now, on the dashboard I don't want to show that date, but rather the days, hours, minutes, and seconds since the rack was inserted. For this, I have to calculate the time difference between the current moment and the timestamp in the database.

The code I'm using is this:

var i;
var nowcomp = new Date().getTime();
var targetdate, tmpdate;
var timestr, elapsed = "";
var yyyy, mm, dd, hh, mn, ss;

for (i = 0; i < 20; i++) {
    timestr = msg.payload[i].PutTime;
    if (timestr !== null){
        yyyy = timestr.substring(0,4).parseInt;
        mm = timestr.substring(4,6).parseInt;
        dd = timestr.substring(6,8).parseInt;
        hh = timestr.substring(8,10).parseInt;
        mn = timestr.substring(10,12).parseInt - 1;
        ss = timestr.substring(12,14).parseInt;
        tmpdate = new Date(yyyy,mm,dd,hh,mn,ss).getTime();
        targetdate = Date.parse(nowcomp) - Date.parse(tmpdate);
        eldays = Math.trunc(targetdate/86400000);
        targetdate = targetdate - (eldays*86400000);
        elhours = Math.trunc(targetdate/3600000);
        targetdate = targetdate - (elhours*3600000);
        elmins = Math.trunc(targetdate/60000);
        targetdate = targetdate - (elmins*6000);
        elsecs = Math.trunc(targetdate/1000);
        elapsed = eldays + "d " + elhours + "h " + elmins + "m " + elsecs + "s";
        msg.payload[i].elapsedTime = elapsed;
        msg.payload[i].targetdate = Date.parse(targetdate);
    } else{
        msg.payload[i].elapsedTime = null;
    }
}

return [msg,{payload:{now:nowcomp}}];

I have two exits, one for getting the objects and one for debugging the time format.

The time format in the second exit looks OK, giving me the time as an absolute value in milliseconds (although node-red switches between that format and others if I click on top of the value).

However, in the output of the function I get the array of objects as expected, but the difference between time values gives me the "NaN" (not a number) result, and therefore I can't calculate the values I need.

This is the output:

image

I've been doing searches online and trying different methods, but nothing seems to work. I saw that there's a moment node, but that currently I can't use the moment methods inside a function node.

Any idea why this is not working and how could I solve it?

Thanks for the input.

Try putting some node.warn(....) statements in the loop to see what your intermediate vars are giving you. At first sight, it looks like you are trying to create variables within the loop without using var or let or const which may have an impact.

I am using "new" in the following statement

tmpdate = new Date(yyyy,mm,dd,hh,mn,ss).getTime();

only because I read in one of the forums I checked that unless I did that, I would not get the integer value, but a whole date object.

Besides that, I'm not declaring any variables or constants inside the loop, they're all declared in the beginning.

I have no idea what's wrong in there.

2 Likes

Appears, amongst others not to be declared unless there is more code you haven't shared? Best to use let or const inside the loop so that you get a clean var each time through the loop (unless your loop is slow).

Put in the warn statements so that you can see what is happening inside the loop.

Okay, so I updated the code to this:

var i;
var nowcomp = new Date().getTime();
node.warn("nowcomp: " + nowcomp);
node.warn("nowcomp parsed: " + Date.parse(nowcomp));
var targetdate, tmpdate;
var timestr, elapsed = "";

for (i = 0; i < 20; i++) {
    let yyyy, mm, dd, hh, mn, ss, eldays, elmins, elsecs;
    timestr = msg.payload[i].PutTime;
    if (timestr !== null){
        node.warn("timestr:" + timestr);
        yyyy = timestr.substring(0,4).parseInt;
        mm = timestr.substring(4,6).parseInt;
        dd = timestr.substring(6,8).parseInt;
        hh = timestr.substring(8,10).parseInt;
        mn = timestr.substring(10,12).parseInt - 1;
        ss = timestr.substring(12,14).parseInt;
        tmpdate = new Date(yyyy,mm,dd,hh,mn,ss).getTime();
        node.warn("tmpdate:" + tmpdate);
        node.warn("tmpdate parsed: " + Date.parse(tmpdate));
        targetdate = Date.parse(nowcomp) - Date.parse(tmpdate);
        node.warn("targetdate: " + targetdate);
        eldays = Math.trunc(targetdate/86400000);
        targetdate = targetdate - (eldays*86400000);
        elhours = Math.trunc(targetdate/3600000);
        targetdate = targetdate - (elhours*3600000);
        elmins = Math.trunc(targetdate/60000);
        targetdate = targetdate - (elmins*6000);
        elsecs = Math.trunc(targetdate/1000);
        elapsed = eldays + "d " + elhours + "h " + elmins + "m " + elsecs + "s";
        msg.payload[i].elapsedTime = elapsed;
        msg.payload[i].targetdate = Date.parse(targetdate);
    } else{
        msg.payload[i].elapsedTime = null;
    }
}

return [msg,{payload:{now:nowcomp}}];

And the output I get for this:


so it is not recognizing the result of the.getTime() method as a number (and neither does after parsing it).

        msg.payload[i].targetdate = Date.parse(targetdate);

has been reduce to seconds by your elhours section.

Try this

var i;
var nowcomp = new Date().getTime();
var targetdate, tmpdate;
var timestr, elapsed = "";
var yyyy, mm, dd, hh, mn, ss;

for (i = 0; i < 20; i++) {
    timestr = msg.payload[i].PutTime;
    if (timestr !== null){
        yyyy = Number(timestr.substr(0,4));
        mm = Number(timestr.substr(4,2));
        dd = Number(timestr.substr(6,2));
        hh = Number(timestr.substr(8,2));
        mn = Number(timestr.substr(10,2));
        ss = Number(timestr.substr(12,2));
        tmpdate = new Date(yyyy, mm - 1, dd, hh, mn, ss).getTime();
        targetdate = nowcomp - tmpdate;
        eldays = Math.trunc(targetdate/86400000);
        targetdate = targetdate - (eldays*86400000);
        elhours = Math.trunc(targetdate/3600000);
        targetdate = targetdate - (elhours*3600000);
        elmins = Math.trunc(targetdate/60000);
        targetdate = targetdate - (elmins*60000);
        elsecs = Math.trunc(targetdate/1000);
        elapsed = eldays + "d " + elhours + "h " + elmins + "m " + elsecs + "s";
        msg.payload[i].elapsedTime = elapsed;
        msg.payload[i].targetdate = Date.parse(targetdate); // this still wont work due to reducing it to milliseconds
    } else{
        msg.payload[i].elapsedTime = null;
    }
}

return [msg,{payload:{now:nowcomp}}];

I think you will find that it has failed to parse the date.

So check to see what your y, m, d, ... values all contain, probably not what you think.

Then have a look at https://www.w3schools.com/jsref/jsref_parseint.asp

Hi Orio,

I didnt study your javascript in depth but if it was me i would go to the original idea of using the Moment js library that makes datetime calculations easier with its extended library.

In order for the module library to be accesible is your function nodes you edit the functionGlobalContext section of the Node-red settings.js file :

 functionGlobalContext: {
           moment:require('moment')
        // os:require('os'),
        // jfive:require("johnny-five"),
        // j5board:require("johnny-five").Board({repl:false})
    },

and in the Function node :

let moment = global.get('moment')

let currentTime = moment();
let sqlTime = moment('20201021075538','YYYYMMDDHHmmss'); // convert to moment
let duration = sqlTime.from(currentTime)

node.warn(currentTime)
node.warn(sqlTime)
node.warn(`Duration : ${duration}`)

//node.send({'payload': `This is the year: ${moment().format('YYYY')} as a string`})

Example flow :

[{"id":"bb4051ca.65a4e8","type":"inject","z":"54aa25ce.417e1c","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":300,"y":380,"wires":[["345d113b.aaa426"]]},{"id":"345d113b.aaa426","type":"function","z":"54aa25ce.417e1c","name":"","func":"let moment = global.get('moment')\n\nlet currentTime = moment();\nlet sqlTime = moment('20101021075538','YYYYMMDDHHmmss');\nlet duration = sqlTime.from(currentTime)\n\nnode.warn(currentTime)\nnode.warn(sqlTime)\nnode.warn(`Duration : ${duration}`)\n\n//node.send({'payload': `This is the year: ${moment().format('YYYY')} as a string`})","outputs":1,"noerr":0,"initialize":"","finalize":"","x":490,"y":380,"wires":[["9cba50f8.4d01d8"]]},{"id":"9cba50f8.4d01d8","type":"debug","z":"54aa25ce.417e1c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":680,"y":380,"wires":[]}]

image

As informed by @Paul-Reed the change node has $moments() in the JSONata expression.
e.g.

[{"id":"c7fe2615.e38278","type":"inject","z":"8d22ae29.7df6d","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"20201015120000","payloadType":"str","x":220,"y":1960,"wires":[["e23e595e.a3d348"]]},{"id":"e23e595e.a3d348","type":"change","z":"8d22ae29.7df6d","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t   $currentTime := $moment().tz('Europe/London').format();\t   $duration := $moment(payload,'YYYYMMDDHHmmss').fromNow($currentTime);\t   {\t    \"current\": $currentTime,\t    \"duration\": $duration\t   }\t)\t\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":440,"y":1960,"wires":[["49952583.65b3fc"]]},{"id":"49952583.65b3fc","type":"debug","z":"8d22ae29.7df6d","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":650,"y":1960,"wires":[]}]

It worked like a charm, thank you very much!

Thank you, it is good to know that I can call functions from that library. The issue is that node-red is running on a company server over which I do not have admin rights, so I am limited to what I can do from the web interface (install new nodes by the "manage pallette" menu is not a problem, but directly accessing the server is out of the question for me, and would like to avoid bothering the sysadmin for issues I can solve in another way).

In any case, should I need other, more advanced stuff regarding times and dates, I'll keep that in mind.

Thank you all for your replies! :smiley:

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