The community will undergo maintenance on October 16th at 10:00 PM PDT and will be unavailable for up to one hour.
Hi all,
I am experiencing some weird behavior with querying property history from the influx DB in thingworx. I am querying several properties at the same time within a time range, some of these are numeric and some are boolean. For the numeric ones I am getting good correct data, but for some of the boolean values the query shows no history and has it as blank. However, the booleans when queried individually show property history and have been flipping between true and false consistently.
I am not sure why this is occurring exactly since the properties have good history but it doesn't show when querying several properties. (I am doing one bigger query because I have a lot of properties that need to be queried in my service).
Any feedback is appreciated!
Added note (I think this is the cause of my issue but I don't know how to work around it) I am seeing a data loss issue when querying properties and using a time range. First image I query this property with a time range and get nothing. Second image I use no time range and you can see that there is value history but I can't get it to query it if I use a time range. Any ideas on how to get around this?
Hi @JK_10744682
In the screenshots above, the date time range you used for searching does not include any values that are visible in the second screenshot.
Why would you expect values then in the first query execution?
You are correct that there aren't any data changes, however the property still had a value during that time range of either true or false and I need to be able to get what the value was during that time range. I think I resolved that part at least by just removing the startDate and getting the most recent value since endDate. I'm just finding that if I query a lot of properties at once and want the values for a specific time range, if there isn't a data change for a property within the time range then no value is returned, rather than returning a row with the actual value at that time which is annoying.
Hi @JK_10744682 ,
I understand what you want to achieve.
It is important to understand that this is not what that service was designed to do: the QueryPropertyHistory returns the historical values of the properties when they changed in a specific time range. It was not designed to give any other value outside of that perimeter.
Is it annoying because it does not return the last value that the property had at the time? Maybe.
If you'd add that, there are some complexities you need to be aware:
1. You need to consider how old would you want to have that last value: e.g.: do you really want to add the last value if that is 2 months old? Same question about the next recorded values.
2. What timestamp would you associate with that value? The original timestamp is too old, is not in the time range you provided...and if you use that you also need to query the property values at that time..not really something to do. Then, you'd add that value with the timestamp = the start of the interval (or end, if you're doing this at the end of the interval). This last option is the one to take, but while it's nice for visualization perspective, you need to keep in mind that even if you see the timestamp, there was no data change recorded at that time - it's a "virtual" value you added for your own use.
Hope it helps.
That makes sense then. For my case I want to get data for a range of 3 minutes and have boolean values that don't change within every 3 minutes but still want to get the value that the boolean held within that time range. I also had numeric values that do change every 3 minutes and wanted to get those as well. The work around I found is to just keep the endDate that I want to use and query that instead of an endDate and startDate range to get the most recent value.
I'm always hesitant not to use start and end dates, due to potential performance problems, so this is the PROTOTYPE code I'm using:
//1. Initializing variables
let startDate = parseDate("2024-05-28 23:06:34", "yyyy-MM-dd HH:mm:ss");
let endDate = parseDate("2024-05-29 00:19:30", "yyyy-MM-dd HH:mm:ss");
let propertyName = "myProperty";
let maxItems = 5;
//2. Main Query service. This one does not return the value we need (by design)
result = me.QueryNumberPropertyHistory({
maxItems: maxItems /* NUMBER {"defaultValue":500} */ ,
propertyName: propertyName /* STRING */ ,
startDate: startDate /* DATETIME */ ,
endDate: endDate /* DATETIME */ ,
quality: undefined /* STRING */ ,
oldestFirst: true /* BOOLEAN */ ,
query: undefined /* QUERY */
});
//3. Add the last known historical value to the display dataset
//3.1. we verify if we have the last logged value in the result
//most of the time it won't be, as the user can't select a start date that is exactly the date when a property was logged
if (result.getRow(0).timestamp != startDate) {
//3.2. Querying backwards the historical dataset, starting from 3 days ago until the start date, sorting with the newest value first
let value = me.QueryNumberPropertyHistory({
maxItems: maxItems /* NUMBER {"defaultValue":500} */ ,
propertyName: propertyName /* STRING */ ,
startDate: dateAddDays(startDate, -3) /* DATETIME */ ,
endDate: startDate /* DATETIME */ ,
quality: undefined /* STRING */ ,
oldestFirst: false /* BOOLEAN */ ,
query: undefined /* QUERY */
}).getRow(0).value;
//3.3. We add this value to the return dataset
//We're not using the original timestamp because we don't want to (possibly) modify the way the chart displays
//in case the latest value was 2 days back
result.AddRow({
value: value,
timestamp: startDate
});
}
//3.4. One last sort based on timestamp, because we added the row at the end of the dataset
let sort = {
name: "timestamp",
ascending: true
};
result.Sort(sort);
Yeah performance for many values is an issue, but I can just get 1 value from the query luckily since I just need the most recent single value which shouldn't affect performance really.
I advise not to use the query object, in case you're speaking of that (I'm not 100% sure).
That query process is always applied at ThingWorx's level, after the initial result is retrieved from the persistence provider database itself.
It's better to limit that initial result with start/end dates, and maxItems, than to apply a query. That's why you see the query:undefined in my code.