Skip to main content
magrawal1-Visitor
1-Visitor
September 14, 2016
Solved

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

  • September 14, 2016
  • 10 replies
  • 11459 views

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

Best answer by gvejandla

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

    }

}

10 replies

nkhose1-Visitor
1-Visitor
September 14, 2016

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

    magrawal1-VisitorAuthor
    1-Visitor
    September 14, 2016

    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

    gvejandla1-VisitorAnswer
    1-Visitor
    September 14, 2016

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

        }

    }

    qngo1-Visitor
    1-Visitor
    September 15, 2016

    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.

    magrawal1-VisitorAuthor
    1-Visitor
    September 15, 2016

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

    bkanipakam-21-Visitor
    1-Visitor
    August 30, 2017

    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

    Terms & ConditionsAccessibility statement