Skip to main content
V
VaibhavShinde16-Pearl
16-Pearl
April 1, 2021
Solved

BETWEEN for two Date Columns TWX 9.1

  • April 1, 2021
  • 2 replies
  • 2454 views

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

 

Best answer by VaibhavShinde

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

2 replies

P
PaiChung22-Sapphire I
22-Sapphire I
April 2, 2021

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

 

S
smainente16-Pearl
16-Pearl
April 2, 2021

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

 

V
VaibhavShinde16-PearlAuthorAnswer
16-Pearl
April 7, 2021

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

S
slangleyCommunity Manager
Support
April 19, 2021

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

Terms & ConditionsAccessibility statement