topic Re: How to query a datatable on both date and time basis. in ThingWorx Developers

How to query a datatable on both date and time basis.

magrawal — Wed, 14 Sep 2016 12:40:48 GMT

Hi,

I have a use case where devices are sending data in JSON format to a broker server and from that broker server we are receiving data to data tables of thingworx through a service.

say we are receiving data for temperature, now I need to generate a time series graph that will display temperature data between the hours of 9 AM to 3 PM, also user will filter data by enter minimum date and maximum date.

So to display filtered data on graph I wrote the following service:

var query = {

"filters": {

"fieldName": "timestamp",

"type": "BETWEEN",

"from": minDate, // Input parameter for minimum date

"to": maxDate // Input parameter for maximum date

}

};

var values = me.CreateValues();

var params = {

maxItems: 500 /* NUMBER */,

values: values /* INFOTABLE*/,

query: query /* QUERY */,

source: undefined /* STRING */,

tags: undefined /* TAGS */

};

// result: INFOTABLE dataShape: "undefined"

var result = me.QueryDataTableEntries(params);

This service is filtering data on the basis of minimum and maximum dates passed by user as Input parameter. Now I am not getting how do I apply filter so that the service will display data only for hours between 9 AM to 1 PM for the input range of date passed by user.

for e.g

I want to display data from 1 september to 13 september only for hours 9 AM to 1 PM for temperature.

1 september and 13 september is passed by user as input parameter.

Looking forward for help.

Thanks,

Meenakshi

Re: How to query a datatable on both date and time basis.

nkhose — Wed, 14 Sep 2016 15:25:53 GMT

Hi ,

As you say you have starttime and endtime datetimepicker widget you should uncheck the initiallizecurrentdatetime checkbox, so before passing to this

query parameters

var query = {

"filters": {

"fieldName": "timestamp",

"type": "BETWEEN",

"from": minDate, // Input parameter for minimum date

"to": maxDate // Input parameter for maximum date

}

};

You may need to use the dateAddhours snippet as because you may need to display records between 9AM & 1PM only

// dateValue:DATETIME

var dateValue = minDate, // Input parameter for minimum date

// dateAddHours(dateValue:DATETIME,amount:NUMBER):STRING

var calculatedDateValueStartValue = dateAddHours(dateValue, 9); // added 9 hours to the min date

Similarly

// dateValue:DATETIME

var dateValue1 = maxDate // Input parameter for maximum date

// dateAddHours(dateValue:DATETIME,amount:NUMBER):STRING

var calculatedDateValueEndValue = dateAddHours(dateValue1, 13); // added 13 hours to the max date

then you can do the usual thing

var query = {

"filters": {

"fieldName": "timestamp",

"type": "BETWEEN",

"from": calculatedDateValueStartValue , // Input parameter for minimum date

"to": calculatedDateValueEndValue // Input parameter for maximum date

}

};

I have not tested this but i think you should give it a go ...!

Hope this helps..!

Re: How to query a datatable on both date and time basis.

magrawal — Wed, 14 Sep 2016 16:14:20 GMT

Hello Nilesh,

Thanks but it's not the solution of my problem.

I need to display data only from 9 AM to 1 PM for all days ranging from 1 september(minDate) to 13 september(maxDate).

The solution you have posted is filtering my data as 2016-09-01 09:00:00 to 2016-09-13 13:00:00

It is displaying all instances of data from 2 september to 12 september whether it is between 9 AM to 1 PM or not.

For each day that falls in range I need to display data only for fix time interval, i.e. 9 AM to 1 PM.

Thanks

Re: How to query a datatable on both date and time basis.

gvejandla — Wed, 14 Sep 2016 20:50:17 GMT

You can filter the results after querying the data table.

// result: INFOTABLE dataShape: "undefined"

var data = me.QueryDataTableEntries(params);

var params = {

infoTableName : "InfoTable",

dataShapeName : "yourdatashape" //change to your datashape name

};

// CreateInfoTableFromDataShape(infoTableName:STRING("InfoTable"), dataShapeName:STRING):INFOTABLE(yourdatashape)

var result = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);

var tableLength = data.rows.length;

for (var x = 0; x < tableLength; x++) {

var row = data.rows;

var newEntry = new Object();

if(row.timestamp.getHours() > 9 && row.timestamp.getHours() < 13){

newEntry.desccription = row.desccription; //change to your required column name

newEntry.count = row.count; //change to your required column name

newEntry.name = row.name; //change to your required column name

result.AddRow(newEntry);

}

Re: How to query a datatable on both date and time basis.

CarlesColl — Thu, 15 Sep 2016 12:39:18 GMT

Just one appointment, if you have the server on a different TimeZone than the User, this code won't give the expected results, as it's executed on server TimeZone.

Re: How to query a datatable on both date and time basis.

magrawal — Thu, 15 Sep 2016 12:43:52 GMT

Then what will the best solution because I have server in different timezone than user.

Re: How to query a datatable on both date and time basis.

qngo — Thu, 15 Sep 2016 13:51:08 GMT

I think about two ways to do this (but not very elegant).

1) With a "for" loop, create a query for each day, retrieve the data of each day and make a UNION of these infotable.

2) With a "for" loop, create a big query with OR and combine queries of each day.

Re: How to query a datatable on both date and time basis.

magrawal — Thu, 15 Sep 2016 15:38:20 GMT

It will also lead an issue of timezone. Users are in different timezone than the server.

Re: How to query a datatable on both date and time basis.

CarlesColl — Thu, 15 Sep 2016 15:51:53 GMT

Then you are out of luck, sorry, TW it's not well prepared for multi-timezone environments.

For this reason we had customized all Standard TW Time Related Widgets to support TimeZones, and also build a server side extension to work with Dates on TimeZones...

Best Luck.

Re: How to query a datatable on both date and time basis.

magrawal — Thu, 15 Sep 2016 17:55:56 GMT

Thanks!.

Re: How to query a datatable on both date and time basis.

bkanipakam-2 — Wed, 30 Aug 2017 04:51:31 GMT

Hello,

I am working on similar use case, But I need to get the data using the rest api call. I need to pass the input time parameters (from date, to date) from the postman or any other rest API testing application, and as a result output I need to get the data for that particular time period.

Is it possible to pass inputs to a service using REST call? Please suggest.

Regards,

Yogesh