ThingWorx Model Definition in Composer > Data Storage > Value Streams > Using the QueryPropertyHistory Service
Using the QueryPropertyHistory Service
The QueryPropertyHistory service queries the value stream for each property (must be a logged property) value of the given source Thing in a given time range. The service calls ValueStream.queryValueStreamEntries for each property, then munges the values together in a result set.
QueryPropertyHistory returns a data set created by querying each logged property on a Thing and then combining the individual datasets into one big data set. A "row" is created for each time stamp in the individual data sets. If there is a logged value with that time stamp, it is entered in the row of the data set. If there is no logged value, by default the last logged value for that property is used. The default value for fillOption parameter is set to Previous. If you select None option in fillOption parameter, then Null value is returned. If there is no logged property; the entry is left blank. This combiner process removes duplicates for a given property based on timestamp. The None option is available from ThingWorx 9.1.13, 9.2.9, and 9.3.4 and later versions. Currently these are the two options available for fillOption parameter.
To obtain the actual logged values for a given property, you must use the QueryPropertyHistory services (for example, QueryIntegerPropertyHistory). You can confirm that data is actually purged with these services. To understand how this service works, several example scenarios are provided below.
Example Scenarios
Scenario 1: I am specifying three rows, but QueryPropertyHistory is returning nine
A value stream is being used to log three properties. There have already been three separate entries for each property. Therefore, the total number of data points entered is nine.
If QueryPropertyHistory is invoked upon this value stream with the maxItems parameter set to three, there will instead be nine rows of data returned. QueryPropertyHistory queried each individual property three times because the maxItems was set to three. Since there were three data entries for each property, the maximum amount of Items returned was possible: nine rows.
Scenario 2: I know I have data at this timestamp, but I'm receiving an empty InfoTable
You can log your own custom timestamp from an Edge device if you do not want to use the one that ThingWorx automatically generates upon a value being entered into the value stream, but the following behaviors should be considered.
What may happen if I try and query on this custom timestamp column instead of using setPropertyVTQ?
The QueryPropertyHistory service goes out to the value stream the Thing is using and grabs an initial result set based on the startDate, endDate, oldestFirst, and maxRows parameters.
If you leave startDate and endDate blank, this means you are not querying on the timestamp property that is automatically generated, and the query will grab the maxRows, let's say 500, from either the most recently added property values or the oldest property values in the value stream. This is determined by the oldestFirst boolean parameter. If oldestFirst is set to false, the returned result set will contain the 500 most recently entered property values for each property. So, with three properties being logged at 500+ values each, the initial dataset will contain 1500 rows.
What happens Post-Processing?
There is one parameter left that has not been used and that is the query parameter. This parameter is applied post-processing. This means that after the initial query on the value stream has completed, that initial result set that is returned then has the query parameter applied to it to filter and only return the desired data.
If you have decided you wanted to query on the custom timestamp property you have created, but the first result set returned does not contain the time you are looking for in the query, nothing will be found and an empty infotable will be returned.
To completely avoid this issue, use the setPropertyVTQ service to input your own timestamp value into the value stream. This overwrites the automatically-generated timestamp values that represent the exact time a value was entered into the value stream. The startDate and endDate parameters can then be used to query on dates instead of the query parameter, and you will not risk losing data from querying on a timestamp.
Was this helpful?