# A tag average over time

**URL:** <https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802>\
**Category:** Ignition\
**Created:** [April 20, 2021, 12:34pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802 "2021-04-20T12:34:39Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![water2](https://avatars.discourse-cdn.com/v4/letter/w/3da27b/32.png) [@water2](https://forum.inductiveautomation.com/u/water2)\
**Post date:** [April 20, 2021, 12:34pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/1 "2021-04-20T12:34:39Z")

</div>

So we have a tag we will call “Data” that is scanning every minute and that data is getting stored on a historical server. That would be 60 data points every hour. What we are trying to do is create an equation that takes an hours worth of data and finds the average. This would be an ongoing equation so most likely an expression tag would work best here probably.

So the equation would either grab data points from the historian or it would sum up the data every minute then obviously to get the average you would divide by the number of numbers so 60 (hour).

My question is how do you write this equation in a way that ignition understands it?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [April 20, 2021, 12:40pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/2 "2021-04-20T12:40:37Z")

</div>

If you used the tag historian to do the recording, then the tag historian will compute averages (and other stats) for you. Either in a binding for display, or via scripting:

[https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagHistory](https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagHistory)

If you didn’t use the tag historian, then you generally would use your DB to perform the average for you, using SQL directly (grouping by hour).

---

<div class="post-metadata">

**Author:** ![water2](https://avatars.discourse-cdn.com/v4/letter/w/3da27b/32.png) [@water2](https://forum.inductiveautomation.com/u/water2)\
**Post date:** [April 20, 2021, 1:51pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/3 "2021-04-20T13:51:47Z")

</div>

This sounds like what I need but looking at the choices I don’t know which one I’d use.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [April 20, 2021, 2:05pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/4 "2021-04-20T14:05:58Z")

</div>

Since you already have it stored in the database, this should be an easy query tag. Assuming your historian is not tag history, this query would give you hourly averages for every hour of the current day (mysql / mariaDB)

```auto
select avg(data) 
from historian_table
where date(t_stamp) = curdate()
group by hour(t_stamp)

```

---

<div class="post-metadata">

**Author:** ![water2](https://avatars.discourse-cdn.com/v4/letter/w/3da27b/32.png) [@water2](https://forum.inductiveautomation.com/u/water2)\
**Post date:** [April 20, 2021, 4:40pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/5 "2021-04-20T16:40:50Z")

</div>

> [@dkhayes117](#):
>
> ```python
> select avg(data) 
> from historian_table
> where date(t_stamp) = curdate()
> group by hour(t_stamp)
> 
> ```

can you also change hour to day?

Also historian table? Is that if you called your database Historian because that's what we did.

---

<div class="post-metadata">

**Author:** ![water2](https://avatars.discourse-cdn.com/v4/letter/w/3da27b/32.png) [@water2](https://forum.inductiveautomation.com/u/water2)\
**Post date:** [April 20, 2021, 4:47pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/6 "2021-04-20T16:47:26Z")

</div>

So this is what it looks like but its not working tag error.

select avg({[.]Water Age Hours})  
from historian\_table  
where date(t\_stamp) = curdate()  
group by hour(t\_stamp)

I have it set as a query tag.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [April 20, 2021, 5:06pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/7 "2021-04-20T17:06:54Z")

</div>

Are you using the built-in tag history logging, or are you storing the tag data with a db connection like Postgre, MySQL, MariaDB, MSSQL, etc?

---

<div class="post-metadata">

**Author:** ![water2](https://avatars.discourse-cdn.com/v4/letter/w/3da27b/32.png) [@water2](https://forum.inductiveautomation.com/u/water2)\
**Post date:** [April 20, 2021, 5:09pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/8 "2021-04-20T17:09:27Z")

</div>

A DB Connection.

We are able to access it through Microsoft SQL Server Management Studio though.

I know a lot about Ignition except when it comes to dealing with SQL server, I know next to nothing.

It looks like in the historian database there is a table for every month dating back to when we first put this online.

 ![Historian](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/b/0bb25e93f47f1db93e508db0d94ed5940ac4d402.jpeg)

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [April 20, 2021, 5:24pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/9 "2021-04-20T17:24:42Z")

</div>

As @pturmel suggested, you will have to use `system.tag.queryTagHistory()`. You could create a gateway timer script to make the calculation then write it to a tag to access the value.

```auto
// Untested code
startDate = system.date.now()
endDate = system.date.addHours(startDate, -1)
avg = system.tag.queryTagHistory(paths=['{[.]Water Age Hours}'], startDate=startTime, endDate=endTime, returnSize=1, aggregationMode="SimpleAverage")
system.tag.writeBlocking(['<PathtoTag>'],[avg])

```

**EDIT** `queryTagHistory()` returns a dataset not a value, so `avg` would need to be altered to get the value out before writing to a tag.

---

<div class="post-metadata">

**Author:** ![water2](https://avatars.discourse-cdn.com/v4/letter/w/3da27b/32.png) [@water2](https://forum.inductiveautomation.com/u/water2)\
**Post date:** [April 20, 2021, 5:56pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/10 "2021-04-20T17:56:28Z")

</div>

> [@dkhayes117](#):
>
> **EDIT** `queryTagHistory()` returns a dataset not a value, so `avg` would need to be altered to get the value out before writing to a tag.

I guess I do not know what you mean by this?

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [April 20, 2021, 6:41pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/11 "2021-04-20T18:41:24Z")

</div>

`tagQueryHistory()` returns a dataset, not a scalar value. We need to get the value out of the dataset, so it would be like `avg[0][1]`. [0] is row number, and [1] is column number. I’m not sure if [0][1] is correct.  
you can add

```auto
print avg[0][1]

```

to see if the correct value is returned, if not it will print the tag name or whatever else is in the dataset

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [April 20, 2021, 7:22pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/12 "2021-04-20T19:22:40Z")

</div>

Since you are using tag history, you have multiple options available, which you use depends on what you are trying to accomplish.

If you want multiple values aggregated to a single time slice(i.e., hourly aggregates for the same Tag of a certain period) then `system.tag.queryTagHistory` is probably the best scripting option.

If you want history over a certain period to be aggregated into a single value per aggregate then `system.tag.queryTagCalculations` is probably the best scripting optoin.

However, if you are trying to use this value on a window or view in a property that accepts a dataset, then consider using a Tag History Binding with the `Aggregation Mode` set to Basic Average.

[https://docs.inductiveautomation.com/display/DOC81/Tag+History+Bindings+in+Vision](https://docs.inductiveautomation.com/display/DOC81/Tag+History+Bindings+in+Vision)  
[https://docs.inductiveautomation.com/display/DOC81/Tag+History+Bindings+in+Perspective](https://docs.inductiveautomation.com/display/DOC81/Tag+History+Bindings+in+Perspective)

---

<div class="post-metadata">

**Author:** ![water2](https://avatars.discourse-cdn.com/v4/letter/w/3da27b/32.png) [@water2](https://forum.inductiveautomation.com/u/water2)\
**Post date:** [April 21, 2021, 12:22pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/13 "2021-04-21T12:22:52Z")

</div>

> [@dkhayes117](#):
>
> ```python
> startDate = system.date.now()
> endDate = system.date.addHours(startDate, -1)
> avg = system.tag.queryTagHistory(paths=['{[.]Water Age Hours}'], startDate=startTime, endDate=endTime, returnSize=1, aggregationMode="SimpleAverage")
> system.tag.writeBlocking(['<PathtoTag>'],[avg])
> 
> ```

So this still did not work for me sadly, still have a tag error. I must have no clue what I am doing when it comes to the query tag.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [April 21, 2021, 12:27pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/14 "2021-04-21T12:27:18Z")

</div>

Unless your tag is of Dataset Type, the write was probably not successful. See @dkhayes117 last post.

Is the tag you are writing to a Query Tag? Because if it is it would write over any write you tried to perform.

Is this script something you’re entering for the Query of a Query Tag, because that would certainly not work.

---

<div class="post-metadata">

**Author:** ![water2](https://avatars.discourse-cdn.com/v4/letter/w/3da27b/32.png) [@water2](https://forum.inductiveautomation.com/u/water2)\
**Post date:** [April 21, 2021, 1:00pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/15 "2021-04-21T13:00:27Z")

</div>

Yeah I’m using SQL Query. Is there a different or better way?

Also I changed the code back to the select code for now, was trying a few things that didnt end up working.

Also the datasource name is historian so is the from correct?

 ![Capture](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/2/32417664d9502e9d07146c641b13e70fd3f37251.png)

---

<div class="post-metadata">

**Author:** ![water2](https://avatars.discourse-cdn.com/v4/letter/w/3da27b/32.png) [@water2](https://forum.inductiveautomation.com/u/water2)\
**Post date:** [April 21, 2021, 1:03pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/16 "2021-04-21T13:03:49Z")

</div>

I have other query tags that work going to a different datasource with the same kind of code.

 ![Capture](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/4/b48a6704a6c4a44ccf7913045e576d083812445f.png)

---

<div class="post-metadata">

**Author:** ![water2](https://avatars.discourse-cdn.com/v4/letter/w/3da27b/32.png) [@water2](https://forum.inductiveautomation.com/u/water2)\
**Post date:** [April 21, 2021, 1:15pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/17 "2021-04-21T13:15:26Z")

</div>

I’m wondering if the tag is actually getting stored with the tag name Water\_Age\_Hours?

In our Compliance Datasource all the tags have actual names like East\_Reservoir\_Effuent\_Meter.

There are no actual names that I can find in the Historian database yet all our graphs and tables in ignition pull data from this datasource and display it.

So I’m wondering if ignition gives it a different naming convention or something.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [April 21, 2021, 1:24pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/18 "2021-04-21T13:24:36Z")

</div>

> [@water2](#):
>
> So I’m wondering if ignition gives it a different naming convention or something.

Tables created by the tag historian are not intended to be queried directly with SQL. All of the data is stored by timestamp and tag id, where the relationship between tag id and the tag itself is stored separately. Use the tag historian bindings or scripting calls to access historian data, not your own SQL.

---

<div class="post-metadata">

**Author:** ![water2](https://avatars.discourse-cdn.com/v4/letter/w/3da27b/32.png) [@water2](https://forum.inductiveautomation.com/u/water2)\
**Post date:** [April 21, 2021, 1:28pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/19 "2021-04-21T13:28:45Z")

</div>

So wait how would you code that out then using the time range I want?

This doesn’t work but I feel like this is close right

startDate = system.date.now()  
endDate = system.date.addHours(startDate, -1)  
avg = system.tag.queryTagHistory(paths=[’{[.]Water Age Hours}’], startDate=startTime, endDate=endTime, returnSize=1, aggregationMode=“SimpleAverage”)  
system.tag.writeBlocking([’’],[avg[0][1]])

I got this in an expression tag.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [April 21, 2021, 1:36pm UTC](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802/20 "2021-04-21T13:36:04Z")

</div>

The first question you need to ask is if you need a tag. What are you trying to accomplish, where is the data going to be used, does it need to be persistent, all of these things (and more) go into determining if you need a tag or not.

[Next page](https://forum.inductiveautomation.com/t/a-tag-average-over-time/45802.md?page=2)