When you have Things with large numbers of properties and/or very frequent logging, it is often difficult to get a sensible data set that can be (time-series) charted in a timely manner at runtime.
We have experienced this with a number of projects, so came up with a service that gives you direct control over the rows returned, and therefore control over the browser performance/end user experience.
We created a service which could directly replace the built-in QueryPropertyHistory service but unlike the built-in service, this respects the startDate, endDate AND maxItems inputs.
Basically what it does is, taking a given startDate and endDate (or applying defaults if none are specified), it will divide the duration of the query up equally by the number rows you want. Next it will query single row values throughout that period and find the values of properties at that time. (The overriding assumption is, for example, that if my remote device tells me a value is 4, it is 4 until the device tells me it isn't 4. That's kind of how value streams work anyway.)
In short, we didn't attempt any interpolation on this version of the service.
If you want to use this service with a time-series chart in a mashup where you can select pens at runtime, you will can to wrap this service again inside a new one that provides a fixed-DataShape, InfoTable output to allow you to attached columns to pens. A second service would be required in the mashup to provide the Label fields for the Chart.
You will create the service on a Thing/Shape/Template and define the same inputs and outputs that the QueryPropertyHistory services use (maxItems (your required row count), startDate, endDate, query, oldestFirst). Default startDate is NOW, default endDate is 24 hours ago - this is done in the code below)
NOTE: If you start using with this with charts, and before you ask the service for 10,000 rows (which won't be fast to execute!), consider how many pixels your chart uses in the browser and remember that the point of this is to limit rows to sensible values (perhaps 1 per 1/2/3 pixels will give you something looks nice. For a most Trends, we've found that 250 to 500 rows is usually more than enough.)
/* *** QueryPropertyHistoryRowLimited *** */
//Does query of a fixed number of value stream points between startDate and endDate
//Does query of a fixed number of value stream points between startDate and endDate
//set default values for properties undefined
var ed = endDate;
var sd = startDate;
var mi = maxItems;
//if endDate is undefined, choose NOW as the endDate
if (ed == null) {
ed = new Date();
}
//if startDate is undefined or greater than the endDate, choose 24 hours ago as the startDate
if ((sd == null) || (sd > ed)) {
var dateValue = new Date();
sd = dateAddHours(dateValue, -24);
}
//if maxItems is null or undefined, set to 100 rows of data as default
if ((mi == null) || (mi == undefined)) {
mi = 100;
}
//calculate the time period between rows of data
var startUTC = Date.UTC(sd.getFullYear(), sd.getMonth(), sd.getUTCDate(), sd.getUTCHours(), sd.getUTCMinutes(), sd.getUTCSeconds());
var endUTC = Date.UTC(ed.getFullYear(), ed.getMonth(), ed.getUTCDate(), ed.getUTCHours(), ed.getUTCMinutes(), ed.getUTCSeconds());
var timeSlice = Math.round((endUTC -startUTC) / (mi - 1));
//setup the date for query on the first loop iteration
var loopDateUTC;
var count = 0;
//define the sort order, based on user selection
if(oldestFirst) {
loopDateUTC = startUTC;
} else {
loopDateUTC = endUTC;
}
//loop through for maxItems-1 iterations and get one row for the named properties each time
for (count=0; count < (mi); count++) {
var loopDate = new Date();
loopDate.setTime(loopDateUTC);
var params = {
oldestFirst: false /* BOOLEAN */,
maxItems: 1 /* NUMBER */,
endDate: loopDate /* DATETIME */,
query: query /* QUERY */,
startDate: undefined /* DATETIME */
};
//if this is the first iteration, create the output table from the first result set
if (count == 0) {
//get one row of data to obtain the structure
var outputTable = me.QueryPropertyHistory(params);
outputTable.RemoveAllRows(); //clear table rows for readding later
}
//get one row of data to add to our table
var propertyHistory = me.QueryPropertyHistory(params);
var row = propertyHistory.getRow(0);
//set the datetime of the row to match the Query endDate
row.timestamp = loopDate; //because we know that timestamp will be our column name
outputTable.AddRow(row);
//prepare for next iteration
if(oldestFirst) {
loopDateUTC = loopDateUTC + (timeSlice);
} else {
loopDateUTC = loopDateUTC - (timeSlice);
}
}
var result = outputTable;
