# How to Select the tag historian data with where clause?

**URL:** <https://forum.inductiveautomation.com/t/how-to-select-the-tag-historian-data-with-where-clause/42729>\
**Category:** Ignition\
**Tags:** ignition80, vision, database\
**Created:** [January 29, 2021, 4:01am UTC](https://forum.inductiveautomation.com/t/how-to-select-the-tag-historian-data-with-where-clause/42729 "2021-01-29T04:01:36Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Shanmukharao.C](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/shanmukharao.c/32/29664_2.png) [@Shanmukharao.C](https://forum.inductiveautomation.com/u/Shanmukharao.C)\
**Post date:** [January 29, 2021, 4:01am UTC](https://forum.inductiveautomation.com/t/how-to-select-the-tag-historian-data-with-where-clause/42729/1 "2021-01-29T04:01:36Z")

</div>

Hello, how to select the tag historian data with where clause?.  
i am logging the machine running status bit along with the other tags, i am logging the data by using tag historization method. now i able to fetch the data between two dates but i required another filter to get the data when the machine is running ?.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/1/016c679646dc43648514b6293095d8846ae470b2.png)

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [January 29, 2021, 5:00pm UTC](https://forum.inductiveautomation.com/t/how-to-select-the-tag-historian-data-with-where-clause/42729/2 "2021-01-29T17:00:10Z")

</div>

Set the aggregation mode to [`DurationOn`](https://docs.inductiveautomation.com/display/DOC81/Tag+History+Bindings+in+Vision#TagHistoryBindingsinVision-AggregationMode)? You don’t really hand-write queries against the historian; it’s _typically_ in a SQL database, but the abstraction is such that it doesn’t have to be. The only way to write a ‘custom’ query is with a [custom tag history aggregate](https://docs.inductiveautomation.com/display/DOC81/Custom+Tag+History+Aggregates) - but there’s some serious hurdles to doing so.

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [January 29, 2021, 5:08pm UTC](https://forum.inductiveautomation.com/t/how-to-select-the-tag-historian-data-with-where-clause/42729/3 "2021-01-29T17:08:07Z")

</div>

> [@paul-griffith](#):
>
> The only way to write a ‘custom’ query is with a [custom tag history aggregate](https://docs.inductiveautomation.com/display/DOC81/Custom+Tag+History+Aggregates) - but there’s some serious hurdles to doing so.

@paul-griffith  
WOAH How did I never see this?  
Could you do something like  
Return Tag1.value if Tag2.value=IntegerParameter?  
If so this will open up sooo many possibilities...

---

<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:** [January 29, 2021, 5:19pm UTC](https://forum.inductiveautomation.com/t/how-to-select-the-tag-historian-data-with-where-clause/42729/4 "2021-01-29T17:19:44Z")

</div>

No, the custom aggregator only gets one tag’s stream of values. There’s nothing that ties values from one tag to states of another tag. That is what transaction groups are for–recording multiple associated tags/values for a single point in time.

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [January 29, 2021, 5:24pm UTC](https://forum.inductiveautomation.com/t/how-to-select-the-tag-historian-data-with-where-clause/42729/5 "2021-01-29T17:24:30Z")

</div>

Damnit.  
I understand the transaction group way, just would be so sweet to be able to ad hoc query against the historian data store like this.  
When you get into the thousands of tags across 100+ gateways, with years of historical data, having transaction groups doing additional logging gets annoying.

I already have a post processing system in place that will take the original returned datasets and filter them down to user selected criteria as above, but it is a secondary step that would be nice to be able to avoid implementing.

---

<div class="post-metadata">

**Author:** ![Eric\_Beltt](https://avatars.discourse-cdn.com/v4/letter/e/71e660/32.png) [@Eric\_Beltt](https://forum.inductiveautomation.com/u/Eric_Beltt)\
**Post date:** [January 13, 2022, 5:50pm UTC](https://forum.inductiveautomation.com/t/how-to-select-the-tag-historian-data-with-where-clause/42729/6 "2022-01-13T17:50:42Z")

</div>

I managed to accomplish this with a series of expression bindings that work together to generate a SQL query as a string, and then execute that query in another binding. FYI, I'm using the results for the data density property in a date-range component, where I only want to show time-stamps when the oven temperature reading was above a minimum threshold to indicate that it was running.

This MYSQL query binding returns the id pointing to the tag in question (most recent history settings):

> SELECT id  
> FROM sqlth\_te  
> WHERE tagpath='{Root Container.Date Range.tagPath}'  
> ORDER BY id DESC  
> LIMIT 1

This expression binding generates a MYSQL where clause using the tag id from above (which is always a float in my case). Note that tMin and tMax are Long data types, using datetimes formatted with the toMillis expression function. They're bound to the outer range of the Date Range component:

> 

```auto
"tagid = '" 
+ {Root Container.Date Range.tagID}
+ "' AND floatvalue >= '"
+ {Root Container.Date Range.threshold}
+ "' AND t_stamp BETWEEN "
+ toStr({Root Container.Date Range.tMin})
+ " AND "
+ toStr({Root Container.Date Range.tMax})

```

This MYSQL query binding returns a dataset of individual queries for each historian partition, each using the where-clause above:

> SELECT  
> CONCAT(  
> "SELECT \* FROM ",  
> pname,  
> " WHERE ",  
> "{Root Container.Date Range.whereClause}"  
> )  
> AS Statement  
> FROM sqlth\_partitions  
> WHERE start\_time \<= '{Root Container.Date Range.tMax}'  
> AND end\_time \>= '{Root Container.Date Range.tMin}'

This expression binding unions the queries above together into a single query:

> groupConcat({Root Container.Date Range.sqlStatements}, "Statement", " UNION ")

Finally, I execute the query and order the results in another MYSQL binding:

> {Root Container.Date Range.sqlStatement}  
> ORDER BY t\_stamp ASC

If you need to reuse this often, you could build the functionality into a UDT or template pretty easily, and of course you can edit the queries for any aggregation you need.