Has anyone successfully used system.tag.QueryTagHistory() to return a fixed number of unmodified values? I want to return the 10 most recent values of a tag whose history I am saving to my db on change.
Just setting the return size on the query function equal to 10 does not accomplish this, instead it divides the time range (in my case I’m not setting the time range, so it’s using the 8 most recent hours) into 10 slices and then does some aggregation/manipulation of the data in each of those time ranges.
Put another way, I want to be able to query tag history like I would use
SELECT TOP(10) t_stamp, myTag FROM myTable ORDER BY t_stamp DESC
But without the headache of doing actual SQL queries on the historian tables.
For reference, this is on a v8 gateway with an MSSQL database.
That would actually be a good feature! I'm also looking for this functionality.
I want to be able to ask the historian:
'Give me the last value before timestamp for tagpath' and also 'Give me the last value after timestamp for tagpath'.
using aggregationMode requires you to not query the data 'asstored', but doing some form of aggregation on your data, which makes it corrupt imo.
Also, by omitting startDate, you will by default query only the last 8 hours, which might be way to much, since you could have 10k values in 8 hours, or way to little, since your value might not have been changing for the last 8 hours. In case of the 10k values returned, the processing might also become way to slow.
I've written a script that will satisfy my needs, with satisfactory performance:
def getNextHistoryValue(path, dateTime):
'''
Args:
path(str): "[default]Sandbox/myValue"
dateTime(datetime ): date to start from
Returns: {
"value": 20,
"timestamp": timestamp of value
}
maxAttempts is how much iterations the script
'''
maxAttempts = 100
interval = 20
value = None
i = 0
nextDate = dateTime
while i < maxAttempts and value == None:
nextDate = system.date.addMinutes(nextDate, interval)
history = system.dataset.toPyDataSet(system.tag.queryTagHistory(
paths = [path],
startDate = dateTime,
endDate = nextDate,
columnNames = ["t_stamp","value"]
))
if len(history) > 0:
print str(history.getColumnNames())
value = history[0]["value"]
timestamp = history[0]["t_stamp"]
return {
"value": value,
"timestamp": timestamp
}
i += 1
return {
"value": None,
"timestamp": None
}
def getPreviousHistoryValue(path, dateTime):
'''
Args:
path(str): "[default]Sandbox/myValue"
dateTime(datetime ): date to start from
Returns: {
"value": 20,
"timestamp": timestamp of value
}
maxAttempts is how much iterations the script
Interval is how long to add to the query
'''
maxAttempts = 100
interval = -20
value = None
i = 0
prevDate = dateTime
while i < maxAttempts and value == None:
prevDate = system.date.addMinutes(prevDate, interval)
history = system.dataset.toPyDataSet(system.tag.queryTagHistory(
paths = [path],
startDate = prevDate,
endDate = dateTime,
columnNames = ["t_stamp","value"]
))
l = len(history)
if l > 0:
print str(history.getColumnNames())
value = history[l-1]["value"]
timestamp = history[l-1]["t_stamp"]
return {
"value": value,
"timestamp": timestamp
}
i += 1
return {
"value": None,
"timestamp": None
}
Even for LastValue ? That seems like it wouldn't do what it's supposed to do, then. I'd even call that a bug - You'd expect the last value to be... the last value.
I don't know what the use case is, but I'd probably cache the value and use that if queryTagHistory returned nothing.
Well, LastValue will give you the last value within your pointcount or periodic rate.
So let's say you get tag history Periodic each minute, you will only get one value for that minute. If you would look at the previous value, you would get a value from a minute earlier, even if there have been 10 more changes within the last minute. That's how it agregates.
That would not work, we really need it to be the previous value and not the current one.
The thing is, I want the last actual value no matter what, not the last value within a determinated pointcount range.
Isn't that the same?
The use case is the following:
We store filepaths of pictures that are in a cloud environment. We store these filepaths in a tag.
We let the user select a time, and we show them the last picture that was stored before the selected time. From this picture, the user can click next or previous, and we'll show the next or previous picture relative to the currently showed one.
Understandable, we do it because we get the filepaths in via Sparkplug B for many devices, which makes historian good for us, since it's also timeseries.
But I got it working with my script above, so I'm satisfied .
.