Hello all,
I have recently been trying to make a bar chart for some data that needs to be organized for a more visually appealing experience, but I am having some issues and am at a standstill.
I currently have a MySQL Table with three columns a code-id, a type-id, and a code-description. And I also have an OPC Tag with Historical Data tracking with MySQL that stores timestamp and code-id. I can take this tag and have it populate a power table as well as have a power table populate with the codes; the idea should be I can use SQL to populate a bar chart that takes the code-description from the MySQL Table and replaces the Historical Tag's code-id with the corresponding description or find a way to access that historical data with SQL. Any guidance would be helpful.
Thank you!
TL;DR = Attempting to find where Historical Tag data can be queried in the MySQL DB.
I would strongly recommend against using dashes to separate words. Use underscores instead.
From chatgpt:
"While using dashes to separate words in SQL table field names is not technically incorrect, it is generally not recommended. Most SQL style guides and conventions recommend using underscores or camel case instead of dashes to separate words in table field names.
Using underscores or camel case helps improve the readability and maintainability of your code, since they are easier to read and parse visually. They also avoid the issue of dashes being interpreted as subtraction operators by SQL engines, which can lead to syntax errors or unexpected results. "
I understand in the database they are named differently and only use underscores. Those are just example field names and aren't any actual data, more or less to get my idea across. Thank you!
Do you have a mockup of what your page will look like? I'm struggling to interpret what you're asking for.
When you say the opc tag has historical data tracking, I assume you mean it has history enabled. If so, what do you mean that it stores the "code-id"? Are you referring to the tagid field that is stored with all timestamped records in the history data tables? (sqlt_data_xxx) Otherwise, how is it storing the code-id field using the Ignition tag historian? This normally will store tag value along with the values for the other fields in the sqlt_data_xxx tables
This table diagram for the Ignition historian tables might help:
I was under the impression that the historian tables might be stored in that table. For some reason, the values that the tag carries do not match unless I am looking at the wrong place (big possibility).
So the way the sqlt_data_x_yyyy_mm data is stored is as below:
However, when I drag the tag itself into a PowerTable in Ignition it is displayed like the following:
Ultimately I just want to be able to query the second table as if it were SQL that way I can do JOINS and other SQL functions. However, all the sqlt_data_x_yyyy_mm tables that I have in MySQL don't match any of the values in the second table.
Thank you for your time!
The tag historian's schema is documented. While it is documented, so you can query directly if you must, you won't get proper analog value reconstructions and you won't get automatic partition unions. Ignition provides historian query functions and bindings to hide this complexity.
Within the data tables, timestamps are stored as 64-bit integer UTC milliseconds of the Unix epoch. This is to bypass the poor datetime and timezone handling of many databases (looking at you MySQL and MS SQL).
While I have read this documentation on the topic I am still confused about what I should do.
That article seems only to go over using different values of Tags to work in one table. However, I am attempting to take the tag historian table/data that I have and use a table that has all of the code's meanings and join them together.
So the PowerTable wont display this (BEFORE):
And rather display this (AFTER):
While the obvious idea would be to use an expression or some way to change the number to words quickly, the table with every code and description has roughly 500 entries, which I would like to avoid entering by hand.
Thank you for helping!
Could you write a query to get the downtimecode mapping from the database, and then use that data to map the values in a python script? You would utilize the built in history querying, write your own query for the code mapping, and then combine the two in python. It would not be as performant as doing everything in SQL, but would be much safer and easier.
2 Likes
If the downtimecode descriptions are static, they could be queried once and cached in a top-level dictionary of a project script module. I would expect the post-process with such a dictionary to be even faster than a SQL join.
2 Likes
FINAL SOLUTION
- Query your tags path in the SQL DB under the sqlth_te table withought the
[brackets].
- Record the Tag's ID.
- Query any of the sqlt_data_1_YYYY_MM tables where tagid equals the tag id.
- All values that have occurred in that month are easily and readily available.
Surprisingly, this was easier than expected; however, note that you may have issues adding multiple months of data from set times.
Thank you to all who helped.
That's why the system functions are there.
I'm not sure from your question why you can't use those ?
