BETWEEN for two Date Columns TWX 9.1

[VaibhavShinde]

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?

Thanks

[PaiChung]

You can use any of the Query services, or the infotable query and use the Between filter

https://support.ptc.com/help/thingworx/platform/r9/en/index.html#page/ThingWorx%2FHelp%2FComposer%2FThings%2FThingServices%2FQueryParameterforQueryServices.html

[smainente]

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

[VaibhavShinde · Accepted solution]

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);

[slangley]

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