deriving timestamp difference from infoTable result

[Ecko665]

Hello,

I have a task where I need to compare 2 rows from an infotable output, and when the value = a number <8> for example, take the timestamp from the first result value of 8 and compare it from the next result of 8.  When a match is found, I need to calculate the difference in milliseconds in a new column.

How can I accomplish this?

Thanks

[DanWolf]

Just off the top of my head, I'd do something like this, assuming a script is populating the InfoTable (otherwise create a Javascript service that takes the InfoTable as input (clones the input table maybe) and outputs the modified InfoTable).  This may contain mistakes, I'm just typing it here without testing.  I'd loop through the table and when I find a value of 8, use a nested loop to look for the next value of 8. Assuming the table has an Integer field called "difference", and you want the difference stored in the first row where the value of 8 is found:

for (let i = 0 ; i < result.rows.length ; i++) {

    if (result[i].value == 8 ) {

        for (let j = i + 1 ; j < result.rows.length ; j++) {

            if (result[j].value == 8 )  {

                result[i].difference = result[i].timestamp - result[j].timestamp; // Difference is in milliseconds, surround with Math.abs() to always get a positive value.

                break;

            }

        }

    }

}

Hope that helps.

PS... had to edit to put a space between 8 and ) because those characters together disappeared in the post, it was probably thinking I'm a teenager trying to use some kind of emote.

[Ecko665 · Accepted solution]

Hey, Thanks for the info.

I'll give it a go and let you know how I make out.  Below is the sample code I have been working with

Atm, I have this service on a thingShape which is added to the Thing itself.  I am filtering the value column on value<8>, as in the code below utilizing queryIntegerpropertyHistory...

var queryFilter = {
"filters":{
"fieldName":"value",
"type":"EQ",
"value": 8
}
};
var params = {
t: result /* INFOTABLE */,
query: queryFilter /* QUERY */
};

// result: INFOTABLE dataShape: "IntegerValueStream"
var result = Things["Asset_Name_Here"].QueryIntegerPropertyHistory({
oldestFirst: undefined /* BOOLEAN */,
maxItems: 500 /* NUMBER */,
propertyName: "stateRegister" /* STRING */,
endDate: endDate /* DATETIME */,
query: queryFilter /* QUERY */,
startDate: startDate /* DATETIME */
});

Thanks again,

Thomas