# How to query historical tag as if it was SQL? **URL:** \ **Category:** Ignition\ **Tags:** ignition81\ **Created:** [May 9, 2023, 5:36pm UTC](https://forum.inductiveautomation.com/t/how-to-query-historical-tag-as-if-it-was-sql/73491 "2023-05-09T17:36:03Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![Pablo\_David](https://avatars.discourse-cdn.com/v4/letter/p/5daacb/32.png) [@Pablo\_David](https://forum.inductiveautomation.com/u/Pablo_David)\ **Post date:** [May 9, 2023, 5:36pm UTC](https://forum.inductiveautomation.com/t/how-to-query-historical-tag-as-if-it-was-sql/73491/1 "2023-05-09T17:36:03Z")
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. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [May 9, 2023, 9:20pm UTC](https://forum.inductiveautomation.com/t/how-to-query-historical-tag-as-if-it-was-sql/73491/2 "2023-05-09T21:20:57Z")
> [@Pablo\_David](#): > > **code-id** , a **type-id** , and a **code-description** 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. " ---
**Author:** ![Pablo\_David](https://avatars.discourse-cdn.com/v4/letter/p/5daacb/32.png) [@Pablo\_David](https://forum.inductiveautomation.com/u/Pablo_David)\ **Post date:** [May 10, 2023, 11:48am UTC](https://forum.inductiveautomation.com/t/how-to-query-historical-tag-as-if-it-was-sql/73491/3 "2023-05-10T11:48:13Z")
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! ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [May 10, 2023, 11:56am UTC](https://forum.inductiveautomation.com/t/how-to-query-historical-tag-as-if-it-was-sql/73491/4 "2023-05-10T11:56:08Z")
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: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/4/04c181e1559ee37a0171485b347465a6adab84b3.png) ---
**Author:** ![Pablo\_David](https://avatars.discourse-cdn.com/v4/letter/p/5daacb/32.png) [@Pablo\_David](https://forum.inductiveautomation.com/u/Pablo_David)\ **Post date:** [May 10, 2023, 12:52pm UTC](https://forum.inductiveautomation.com/t/how-to-query-historical-tag-as-if-it-was-sql/73491/5 "2023-05-10T12:52:02Z")
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: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/e/5e93fd007b77175e5507d592f9a266cde7269b28.png) However, when I drag the tag itself into a PowerTable in Ignition it is displayed like the following: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/2/320cfb51fb28832655a4257fb55f2fec68c4ebbc.png) 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! ---
**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:** [May 10, 2023, 1:08pm UTC](https://forum.inductiveautomation.com/t/how-to-query-historical-tag-as-if-it-was-sql/73491/6 "2023-05-10T13:08:14Z")
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). ---
**Author:** ![Pablo\_David](https://avatars.discourse-cdn.com/v4/letter/p/5daacb/32.png) [@Pablo\_David](https://forum.inductiveautomation.com/u/Pablo_David)\ **Post date:** [May 10, 2023, 1:24pm UTC](https://forum.inductiveautomation.com/t/how-to-query-historical-tag-as-if-it-was-sql/73491/7 "2023-05-10T13:24:35Z")
While I have read [this documentation](https://docs.inductiveautomation.com/display/DOC80/Tag+History+Bindings+in+Vision) 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** ): ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/2/320cfb51fb28832655a4257fb55f2fec68c4ebbc.png) And rather display this ( **AFTER** ): ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/2/f2247f2fbfeab4251443fe6cd4503421682f1069.png) 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! ---
**Author:** ![djs](https://avatars.discourse-cdn.com/v4/letter/d/e47774/32.png) [@djs](https://forum.inductiveautomation.com/u/djs)\ **Post date:** [May 10, 2023, 1:54pm UTC](https://forum.inductiveautomation.com/t/how-to-query-historical-tag-as-if-it-was-sql/73491/8 "2023-05-10T13:54:01Z")
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. ---
**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:** [May 10, 2023, 2:21pm UTC](https://forum.inductiveautomation.com/t/how-to-query-historical-tag-as-if-it-was-sql/73491/9 "2023-05-10T14:21:42Z")
> [@djs](#): > > It would not be as performant as doing everything in SQL, but would be much safer and easier. 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. ---
**Author:** ![Pablo\_David](https://avatars.discourse-cdn.com/v4/letter/p/5daacb/32.png) [@Pablo\_David](https://forum.inductiveautomation.com/u/Pablo_David)\ **Post date:** [May 10, 2023, 6:10pm UTC](https://forum.inductiveautomation.com/t/how-to-query-historical-tag-as-if-it-was-sql/73491/10 "2023-05-10T18:10:58Z")
FINAL SOLUTION 1. Query your tags path in the SQL DB under the **sqlth\_te** table withought the `[brackets]`. 2. Record the Tag's ID. 3. Query any of the **sqlt\_data\_1\_YYYY\_MM** tables where _tagid_ equals the tag id. 4. 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. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [May 10, 2023, 6:14pm UTC](https://forum.inductiveautomation.com/t/how-to-query-historical-tag-as-if-it-was-sql/73491/11 "2023-05-10T18:14:50Z")
> [@Pablo\_David](#): > > note that you may have issues adding multiple months of data from set times. That's why the system functions are there. I'm not sure from your question why you can't use those ?