10 Replies Latest reply on Aug 30, 2017 12:51 AM by bkanipakam-2 RSS
    magrawal Apprentice

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

    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 Creator

        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.
          qngo Creator

          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.
            bkanipakam-2 Explorer

            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