I am still trying to figure out how to use the tag historian.
for example
I get a tag for an event code.
If it is like 2210 then I know the part was too small, shrunk too much
2211 too big, didn’t shrink enough
2212 missing a hole for the keychain
2213 deformed from the mold
I also have a tag that turns to 1 when a new event happens, and 0 when there isn’t a new event.
I have some ints for date and time.
how do I get the number of occurrences per shift and per hour of each code?
On the labels I display, do I make a binding for tag historian query?
I disagree, I think tag history is great to use for what you need.
For your event, if you trust that you won't have multiple 1s or 0s in a row, then use count aggregate for one hour interval. If you could have multiples, then you will want to use a script. Something like the one below that @JordanCClark did, but instead of total time, you would change to total count.
You are going to have some difficult because you are trying to do two different calculations at once. Your tag that is 1 when an event happens can be pretty easily done with a tag calculation query, but the count of the occurrences of the codes is another story.
If it were me i would do it with regular sql query. You can actually perform your aggregation for the two different types of calculations, then join/union your results together.
Here’s a example query using a tag that is logged with historian, with partitioning off. This will get you your code counts by the hour. Im sure there is a way to do this with partitioning enabled.
SELECT tagpath, from_unixtime(t_stamp/1000) as tstamp, intvalue, count(intvalue) as codecount FROM rounds_test.sqlth_1_data
inner join sqlth_te on sqlth_1_data.tagid = sqlth_te.id
where tagpath like 'codetag'
group by intvalue, hour(from_unixtime(t_stamp/1000))
Also if you want to have this data span long time periods you will need to ammend the group by to include day, year. You could also not do the aggregation here, but instead do it on the table using grouping.
Now that you have gotten more specific on what you have, I think a transaction group is your only choice. You will need to build the data, then send when ready. Initially it sounded like the data was separate.
Nevermind, I think I will write a counter in the plc.
When I read the counter goes up, I know there is a new event code.
Then I will write a script that reads the code.
Make a bunch of tags, then add 1 to the appropriate tag.
Just not sure how to do the 1 hour total. It seems like tag historian has a 1 hour count option, but I don’t know if it works across a reset. Like if the value were 9, then shift change reset it to 0, would the tag history binding count the ones that happened in the last hour and be 4 or however many were just in the last hour?
Yes, make some kind of trigger that indicates a new record/part/whatever has been processes so you know it’s a new part even if the event code doesn’t change
Then just create a transaction group with a Execution time lower than the shortest time between two records could be in your process:
Store the timestamp for each record:
Then you can assign that trigger value as a trigger for the transaction group, something like this:
Then you can just do a aggregate by hour with a SQL query once you have records with timestamps.
I don’t understand many ways to make the tag historian useful.
So many tags are historized with expectations that it would be so useful.
I open a table on perspective, bind the tag history, and it shows the t_stamp in epoc.
I got some things to be useful in tag historian. Like I historized the tags.
Then on an object in perspective, I setup a few properties to show arrays of all the values at a certain time in history, then another array at a different time to get the difference.
Is it just me, or are transaction groups a lot better if I am doing anything other than a line graph?
I am about to make a transaction group for tags that are being historized…
I’m on the other side of the fence in that i find the historian much more useful than transaction groups. I come from a SQL background, so i am much more comfortable manipulating the data with queries, and transaction groups you don’t necessarily need to have any SQL knowledge. That said you don’t actually need to use either, you can write data however you like with scripting.
if you have a tag historized that shows you the reason for a stop as a 4 digit number, how do you get the top five stops in the last month with the historian?
I was taught how to do it with the transaction groups in a named query
SELECT Top 40 myCode, sum(myQuantity) as Quantity, count(myCode) as myCode_Count
FROM myTable
where ( t_stamp > DATEADD(minute, -5000, GETDATE()))
Group by myCode
order by sum(myQuantity) DESC;
it is like this for the top 40 I believe
I tried to delete the post I had before this.
Are people able to see that post how it was before I had deleted it?
Trying to keep my code generic.
Anything you can do with a transaction group you can do with a gateway timer script or a gateway tag change event. Scripted manipulation of wide tables can perform more complicated and/or more robust handshaking with PLCs than transaction groups.
If you only care about a particular data item's relationship to time, and you only need to store it when it changes, then it is a good candidate for the tag historian.
If the value represents a process reading (analog perhaps) that has a bit of jitter you can ignore, then it is a very good candidate for the tag historian.
If you care about a particular data item's relationship to other items, they probably need to be sampled and stored together. Use a wide table, not the historian. If sampling on a trigger, use OPC Read mode in the transaction group, or system.opc.read*() in a script, to ensure you have the associated data from after the trigger change.
If you need the data stored in a SQL DB in a predictable structure for non-Ignition access, avoid the tag historian. Unless you are willing to implement a web API with the webdev module to deliver data to external consumers.
Somethings are best in the historian, others in transaction groups, and yet other times it is easier to script it based on what is going on when that data needs to be logged. Part of the trick is knowing when to use what technology.
