Report - Query Historical Data stored with storeTagHistory function

I have two totalized flows. At midnight a Gateway Event script stores the now previous day’s total in the MySQL historical database. It is stored with:


t_stamp is set to noon of the day. I’m attempting to create a report with a table and graph with both flows displayed. The problem comes in when I setup the tag history query in the “Data” tab of the report. I select both tags, Closest Value for aggregation mode, and OnChange for the Sample size. This creates a double entry for my flows because the change on flow1 triggers a result from flow2 and vice versa. As a work around I setup two querys but then I cannot display them in the same chart or table because I must bind it to one query. How can I design a query for this data?

If I run this query from the Database Query Browser tool then I get a nice tidy result with the data I want

SELECT *, (SELECT FROM_UNIXTIME(t_stamp/1000)) FROM sqlt_data_1_2021_12 WHERE tagid = '120' or tagid = '125'

How would you solve this? I’m new to all of this so my whole approach might be completely off.

I wouldn’t put such data in the tag historian. Just make a table for daily summary values.

Certainly sounds like the answer and will try to do this. I’m a n00b and don’t really know how to do this. How do I store the data into the table? Not looking to for my hand held, just point me in the right direction.

The answer for drag-and-drop, point-and-click setup is to use a transaction group (from the SQL Bridge module) and schedule it for midnight. It will even create the table for you in your database.

But since you are already using a scheduled script of some kind, you can use system.db.runPrepUpdate() with a SQL INSERT statement and your column values. You would have to create the table in your DB manually.

Perfect. I started this whole mess with a transaction group but did not have the SQL Bridge Module license. Probably should have just ponied up the $$. On the bright side I guess I learned something…,