cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Learn all about PTC Community Badges. Engage with PTC and see how many you can earn! X

BETWEEN for two Date Columns TWX 9.1

VaibhavShinde
16-Pearl

BETWEEN for two Date Columns TWX 9.1

Hi ,

I am looking to retrieve shiftName value from current datetime as below .

if Current date is between Start_Date and End_time then return shift name.

how to do it using query or any functions in TWX?

VaibhavShinde_0-1617274819893.png

Thanks

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @smainente & @PaiChung ,

Thanks for the reply.

Below code works for me..

var query = {
"filters":{
"type": "LT",
"fieldName": "Start_Date",
"value": date
}
};

var result1= Things["AAL.MPS.ShiftConfig.DT"].QueryDataTableEntries({
values: undefined /* INFOTABLE */,
maxItems: undefined /* NUMBER */,
tags: undefined /* TAGS */,
source: undefined /* STRING */,
query: query /* QUERY */
});


var query1 = {
"filters":{
"type": "GT",
"fieldName": "End_Time",
"value": date
}
};

// Provide your filter using the format as described in the help topic "Query Parameter for Query Services"
var params = {

t: result1 /* INFOTABLE */,

query: query1 /* QUERY */

};
var result2 = Resources["InfoTableFunctions"].Query(params);

View solution in original post

4 REPLIES 4

Just some addition to @PaiChung comment (I don't know if it is relevant to your use case - maybe you just want to manipulate an in memory InfoTable )

 

It is important to know that if you use a query filter on a service that queries persisted data (Steam, DataTable), the filter is always applied in-memory on the platform.

For example :

var query = {...};
var result =  Things["myStream"].QueryStreamEntriesWithData({maxItems: 1, query: query});

If "myStream" contains 5 millions entries, 5 millions records will be fetched from the DB, then filtered by the platform.

 

See https://community.ptc.com/t5/IoT-Tech-Tips/Note-about-Data-Tables-and-Streams-Query-APIs/td-p/691120

 

Hi @smainente & @PaiChung ,

Thanks for the reply.

Below code works for me..

var query = {
"filters":{
"type": "LT",
"fieldName": "Start_Date",
"value": date
}
};

var result1= Things["AAL.MPS.ShiftConfig.DT"].QueryDataTableEntries({
values: undefined /* INFOTABLE */,
maxItems: undefined /* NUMBER */,
tags: undefined /* TAGS */,
source: undefined /* STRING */,
query: query /* QUERY */
});


var query1 = {
"filters":{
"type": "GT",
"fieldName": "End_Time",
"value": date
}
};

// Provide your filter using the format as described in the help topic "Query Parameter for Query Services"
var params = {

t: result1 /* INFOTABLE */,

query: query1 /* QUERY */

};
var result2 = Resources["InfoTableFunctions"].Query(params);

Hi @VaibhavShinde.

 

If you feel your question has been answered, please mark the appropriate response as the Accepted Solution for the benefit of others with the same question.

 

Regards.

 

--Sharon

Top Tags