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 ?.
Set the aggregation mode to DurationOn? 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 - but there’s some serious hurdles to doing so.
@PGriffith
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...
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.
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.
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:
"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.
1 Like