How to query a datatable on both date and time basis.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
-
Troubleshooting
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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);
}
}
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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..!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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);
}
}
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Then what will the best solution because I have server in different timezone than user.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks!.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
It will also lead an issue of timezone. Users are in different timezone than the server.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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