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

[magrawal]

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

[nkhose]

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..!

[magrawal]

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

[gvejandla · Accepted solution]

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

    }

}

[CarlesColl]

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.

[magrawal]

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

[CarlesColl]

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.

[magrawal]

Thanks!.

[qngo]

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.

[magrawal]

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

[bkanipakam-2]

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