Issue with query property history service

[AViñeta]

I am trying to query the property history of a thing by date but as soon as i pass query parameters it stops working correctly, dropping rows or properties without rhyme or reason when querying without parameters works alright as seen in the images attached it also ignores the max items parameter. Any way to solve it without having to write a cumbersome service to bypass it? 

[VladimirRosu · Accepted solution]

Hi @AViñeta,

Query Property History is a bit strange to work with if you use it the first times.

There is good documentation available in the help center here: https://support.ptc.com/help/thingworx/platform/r9/en/index.html#page/ThingWorx/Help/Composer/DataStorage/ValueStreams/UsingTheQueryPropertyHistoryService.html  and as a Technical Support article here, https://www.ptc.com/en/support/article/cs226000

But, in short:

The maxItems means the max number of rows that the service will use internally to get from the Value Stream data. If you have 10 properties, the service will get the last [maxItems] number of data points for each property, and then (this is where it gets interesting) it will combine the resulted 10 tables into a single one that you see in Composer. The combination algorithm makes sure that for each unique timestamp it tries the best it can to populate values for ALL properties - even if the timestamps are different. That's the tricky part: depending on how different the timestamps are, you could have either 0 rows (minimum) or 100 rows (10 properties x 10 different timestamps). It all depends on the timestamps you store in the Value Stream. And, also, depending on the start or end Date, you might end-up with missing values, if the algorithm can not locate a proper value.  Remember that when maxItems is empty is also replaced with a default of 500 rows.

Regardless, the links I shared above probably will provide enough information to help you bypass this issue, with some experimentation.

[AViñeta]

Thank you, for the reply, form what I've understood as long as the timestamps for the different properties are clearly grouped together it will work as I expected it to work, but if there are enough updates in a short enough timespan relative to the dataset it will struggle to do so? Also from what I have read the property reasonLatchDesc is blank up to a certain entry because that is the first timestamped value in the dataset and the service did not look further right?