# Querying tag history by quantity, not time range (with scripting)

**URL:** <https://forum.inductiveautomation.com/t/querying-tag-history-by-quantity-not-time-range-with-scripting/34627>\
**Category:** Ignition\
**Tags:** ignition80, scripting\
**Created:** [May 11, 2020, 1:34pm UTC](https://forum.inductiveautomation.com/t/querying-tag-history-by-quantity-not-time-range-with-scripting/34627 "2020-05-11T13:34:05Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![paul.mangels](https://avatars.discourse-cdn.com/v4/letter/p/a698b9/32.png) [@paul.mangels](https://forum.inductiveautomation.com/u/paul.mangels)\
**Post date:** [May 11, 2020, 1:34pm UTC](https://forum.inductiveautomation.com/t/querying-tag-history-by-quantity-not-time-range-with-scripting/34627/1 "2020-05-11T13:34:05Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![Benjamin\_Sperl](https://avatars.discourse-cdn.com/v4/letter/b/aeb1de/32.png) [@Benjamin\_Sperl](https://forum.inductiveautomation.com/u/Benjamin_Sperl)\
**Post date:** [December 8, 2021, 10:05pm UTC](https://forum.inductiveautomation.com/t/querying-tag-history-by-quantity-not-time-range-with-scripting/34627/2 "2021-12-08T22:05:21Z")

</div>

I would also like to be able to do this. The closest I’ve gotten is:

```auto
system.tag.queryTagHistory(paths=pathList, startDate=startTime, endDate=endTime, returnSize=1, aggregationMode="LastValue", returnFormat='Tall')

```

But this seems to do some interpolation and gives inconsistent results.

---

<div class="post-metadata">

**Author:** ![louagejasper](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/louagejasper/32/86311_2.png) [@louagejasper](https://forum.inductiveautomation.com/u/louagejasper)\
**Post date:** [January 20, 2023, 2:58pm UTC](https://forum.inductiveautomation.com/t/querying-tag-history-by-quantity-not-time-range-with-scripting/34627/3 "2023-01-20T14:58:20Z")

</div>

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'.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [January 21, 2023, 7:49am UTC](https://forum.inductiveautomation.com/t/querying-tag-history-by-quantity-not-time-range-with-scripting/34627/4 "2023-01-21T07:49:36Z")

</div>

> [@louagejasper](#):
>
> 'Give me the last value before _timestamp_ for tagpath'

There's a `LastValue` aggregation mode:

```python
system.tag.queryTagHistory(
    paths,
    endDate=timestamp,
    aggregationMode = 'LastValue'
)

```

> [@louagejasper](#):
>
> 'Give me the last value after _timestamp_ for tagpath'

Wouldn't that just be the very last value ?

---

<div class="post-metadata">

**Author:** ![louagejasper](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/louagejasper/32/86311_2.png) [@louagejasper](https://forum.inductiveautomation.com/u/louagejasper)\
**Post date:** [January 21, 2023, 8:10am UTC](https://forum.inductiveautomation.com/t/querying-tag-history-by-quantity-not-time-range-with-scripting/34627/5 "2023-01-21T08:10:58Z")

</div>

I have to correct you there.

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:

```auto
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
	}

```

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [January 21, 2023, 11:51am UTC](https://forum.inductiveautomation.com/t/querying-tag-history-by-quantity-not-time-range-with-scripting/34627/6 "2023-01-21T11:51:50Z")

</div>

> [@louagejasper](#):
>
> using aggregationMode requires you to not query the data 'asstored', but doing some form of aggregation on your data, which makes it corrupt imo.

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.

> [@louagejasper](#):
>
> 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

I don't know what the use case is, but I'd probably cache the value and use that if `queryTagHistory` returned nothing.

---

<div class="post-metadata">

**Author:** ![louagejasper](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/louagejasper/32/86311_2.png) [@louagejasper](https://forum.inductiveautomation.com/u/louagejasper)\
**Post date:** [January 21, 2023, 12:38pm UTC](https://forum.inductiveautomation.com/t/querying-tag-history-by-quantity-not-time-range-with-scripting/34627/7 "2023-01-21T12:38:29Z")

</div>

> [@pascal.fragnoud](#):
>
> 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.

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.

> [@pascal.fragnoud](#):
>
> I don't know what the use case is, but I'd probably cache the value and use that if `queryTagHistory` returned nothing.

That would not work, we really need it to be the previous value and not the current one.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [January 21, 2023, 1:08pm UTC](https://forum.inductiveautomation.com/t/querying-tag-history-by-quantity-not-time-range-with-scripting/34627/8 "2023-01-21T13:08:29Z")

</div>

> [@louagejasper](#):
>
> Well, LastValue will give you the last value within your pointcount or periodic rate.

I fail to see how that's not what you want.  
I'm confused, but I don't have an ignition installed here so I can't test anything to clarify.

> [@louagejasper](#):
>
> That would not work, we really need it to be the previous value and not the current one.

What do you mean, the previous one ? You were talking about the last value, not the previous one...

What's your use case, exactly ? Do you need to fetch that last value at 'random' times, in a 'random' time window ?

---

<div class="post-metadata">

**Author:** ![louagejasper](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/louagejasper/32/86311_2.png) [@louagejasper](https://forum.inductiveautomation.com/u/louagejasper)\
**Post date:** [January 23, 2023, 10:17am UTC](https://forum.inductiveautomation.com/t/querying-tag-history-by-quantity-not-time-range-with-scripting/34627/9 "2023-01-23T10:17:07Z")

</div>

> [@pascal.fragnoud](#):
>
> I fail to see how that's not what you want.

The thing is, I want the last actual value no matter what, not the last value within a determinated pointcount range.

> [@pascal.fragnoud](#):
>
> You were talking about the last value, not the previous one...

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.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [January 23, 2023, 10:35am UTC](https://forum.inductiveautomation.com/t/querying-tag-history-by-quantity-not-time-range-with-scripting/34627/10 "2023-01-23T10:35:24Z")

</div>

I'd probably not use the historian for that, but a dedicated table in your database.

---

<div class="post-metadata">

**Author:** ![louagejasper](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/louagejasper/32/86311_2.png) [@louagejasper](https://forum.inductiveautomation.com/u/louagejasper)\
**Post date:** [January 23, 2023, 10:47am UTC](https://forum.inductiveautomation.com/t/querying-tag-history-by-quantity-not-time-range-with-scripting/34627/11 "2023-01-23T10:47:52Z")

</div>

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 🙂 .