Solved! Go to Solution.
Lots of conditions! @VladimirRosu gave you a good option here, and mine would be similar in the sense you get the data using QPH and engineer the result by manually adding rows if needed.
First would query without a startdate and maxItems=2, oldestfirst=false. Enddate would be 7AM. This will give you at least two rows, always. Then you check if the first row has a date of exactly 07:00:00:000 (unlikely, but possible) and if so you duplicate the value with a new timestamp.
That's a very long piece of code for people to study. I would recommend to reduce it to the necessary. This should also help you in identifying the problem.
Also note there is a formatting option for source code in the forum editor so you can make it more readable.
Hi Rocko, I have update the code please check now.
My I ask why you are doing it this way instead of just assigning a value like this:
Things[Thingname][Propertyname]=true; // or false, whatever your new value is
This will automatically set the value for the current time.
My requirement is whenever i query by providing x timestamp it should return someresult instead of empty infotable
This method will not log the property if the values have remained the same for the past hour, as it only tracks changes when the datachange type is set to value
Ok, but that's a different question. UpdatePropertyValues is working to specification. You might want to look at https://support.ptc.com/help/thingworx/platform/r9.6/en/#page/ThingWorx/Help/Composer/Things/ThingProperties/ThingProperties.html:
In general, a property can be updated in Composer by setting me.PropertyName = value, and the timestamp will reflect
the current timestamp of the server. For historical updates, you can use the UpdatePropertyValues and
UpdatePropertyValuesBatched service
You do not need to add the same value over and over again - QueryPropertyHistory has a parameter called fillOption which should give you the "active" value for any point in (past) time.
Hi Rocko,
I don’t want to use queryPropertyHistory, as it returns a list of all properties in the Thing, and the fill option is only available with queryPropertyHistory. However, I’ve achieved my desired outcome (logging properties with the current timestamp regardless of the data change type) by using an insert query directly on the database assigned to the value stream.
That is risky, because when you directly write to the DB, you kinda "void the warranty" - it's not a supported use case, and there might be unwanted side effects.
I recommend looking into how much slower QueryPropertyHistory is in comparison to what you use (you don't need fastest, you need fast enough) and then decide if it's worth the risk.
I understand the situation, but how can I achieve this using queryPropertyHistory when there are no value changes in the past 4 hours? I still want queryPropertyHistory to return some rows and i want to query single property at a time.
For example, I have a property named Availability with the data change type set to value. The value was updated and logged at these times:
2024-10-04 06:36:57.468: the value changed from 0 to 1
2024-10-04 06:37:02.555: the value changed from 1 to 5
When I use queryPropertyHistory with a StartDate of 2024-10-04 00:05:00.000 and an EndDate of 2024-10-04 00:07:00.000, it returns some rows, as expected. However, when I query between StartDate 2024-10-04 00:07:00.000 and EndDate 2024-10-04 00:10:00.000, it returns 0 rows, even though I am using the filloption as Previous. This happens because no data changes occurred between 07:00 and 10:00, and no entries were logged in the value stream.
What I want to achieve is that the query should return at least two rows:
One with the StartDate and Another with the last logged value (which is 5 in this case).
While agreeing this is needed because of how QueryProperty* services work, the change is relatively simple to implement.
You won't need to alter the data store (Value Stream) for this to work, and it's quite fast.
However, you need to do some design decisions:
do you really want to display the last known historical value even if it’s too far in the past, like 3 days in the past?
//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 = "Channel1_Device1_Tag2";
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);
Lots of conditions! @VladimirRosu gave you a good option here, and mine would be similar in the sense you get the data using QPH and engineer the result by manually adding rows if needed.
First would query without a startdate and maxItems=2, oldestfirst=false. Enddate would be 7AM. This will give you at least two rows, always. Then you check if the first row has a date of exactly 07:00:00:000 (unlikely, but possible) and if so you duplicate the value with a new timestamp.
Hello @leekhil_kataray,
It looks like you have some responses from a community member. If it helped to answer your question please mark the appropriate reply as the Accepted Solution.
Of course, if you have more to share on your issue, please let the Community know so other community members can continue to help you.
Thanks,
Vivek N.
Community Moderation Team.
If you have DataChange set to Value, then any property updates that you push (via the service you mentioned or any other way) would need to have a different value to be recorded in the property (meaning to be visible in composer and in the associated Value Stream, if you enabled Logging).
Can you specify if the value you're attempting to set is different to the value that is already there on the property ?
Because if not, then the behavior would be as expected.
You can also test this quickly by setting the DataChange to "Always".
There's another thing that you would need to specify, and that is how exactly are you verifying if the property update was successful? If you use composer, with a date in the past, the system will not display it in the Properties tab, but you can see it when using QueryPropertyHistory types of services.
Hi VladimirRosu,
I’m trying to store the same value for a property. I've been using UpdatePropertyValue to update property values for a previous timestamp, assuming that data changes wouldn't affect this service, whether the value is the same or different, since it's for a past timestamp.
However, I've decided to take a different approach. I’m now logging the property at the current timestamp, regardless of the data change type or the property value. To ensure the value is always logged, I’m directly writing an insert query into the database.
Got it.
As @Rocko said above, inserting in the DB is dangerous - simply because we don't guarantee the schema change across versions and when that changes, you'd need to refactor.
Logging a property at the current timestamp, regardless of value can be done if you set the DataChange as Always - this would be the official approach and it's just a checkbox. Just for this alone, there's no reason in this case to go to SQL queries
Hi VladimirRosu,
I agree with what you and Rocko have suggested, but my IoT gateway is sending values every 2 seconds. If I set the dataChange option to "always," it will result in a lot of redundant data, leading to unnecessary storage usage in the database.
That's why I've set dataChange to "value." In this case, if there is no change in the data, nothing gets logged for that specific time. However, I still need a data point to be logged every 15 minutes for further processing.
What I want is, if no data has been logged in the last 15 minutes, I want to force a log using the updatePropertyValues service. This way, even if there is no change, I can still ensure consistent data logging for my use case.
However, I am unable to achieve this.