# Query problem: monthly change of historical db **URL:** \ **Category:** Ignition\ **Created:** [May 22, 2013, 9:38am UTC](https://forum.inductiveautomation.com/t/query-problem-monthly-change-of-historical-db/6554 "2013-05-22T09:38:30Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![max7184](https://avatars.discourse-cdn.com/v4/letter/m/aeb1de/32.png) [@max7184](https://forum.inductiveautomation.com/u/max7184)\ **Post date:** [May 22, 2013, 9:38am UTC](https://forum.inductiveautomation.com/t/query-problem-monthly-change-of-historical-db/6554/1 "2013-05-22T09:38:30Z")
Good morning at all ![:mrgreen:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/d23659b203d2d24d394be983a52adb2fa6fe23d4.gif "Mr. Green") ![:scratch:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/76d1620bec252bfac07050c889ff5a91dc8a9c25.gif ":scratch:") i have this problem: I need to calculate the volume of water in a pump, given the flow rate (contained in a historical tag). I need to calculate the daily volume (last 24 hours) I need to calculate the monthly volume (last 30 days) I need to calculate the annual volume (last 365 days) Currently the query tag for the calculation of 24h is as follows: `select (sum(my_avg)*3600) from ( SELECT avg(sqlt_data_1_2013_05.floatvalue) as my_avg,(from_unixtime(sqlt_data_1_2013_05.t_stamp/1000)) as my_data FROM ignition.sqlt_data_1_2013_05 where sqlt_data_1_2013_05.tagid = 4856 and from_unixtime(sqlt_data_1_2013_05.t_stamp/1000) <= (select current_timestamp) and from_unixtime(sqlt_data_1_2013_05.t_stamp/1000) >= date_sub(curdate(), interval 24 hour) group by hour(from_unixtime(sqlt_data_1_2013_05.t_stamp/1000)) ) as tbl1` How can I modify it, in order to automate the search in the tables above, as regards the monthly volume and yearly volume? I also have the same problem, I suppose, to the change of month, about the daily calculation ![:scratch:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/76d1620bec252bfac07050c889ff5a91dc8a9c25.gif ":scratch:") ![:scratch:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/76d1620bec252bfac07050c889ff5a91dc8a9c25.gif ":scratch:") ![:scratch:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/76d1620bec252bfac07050c889ff5a91dc8a9c25.gif ":scratch:") ![:prayer:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e5a7e3b63facc151ea73e3b45db7bdb3d4bdc316.gif ":prayer:") ![:/](https://us1.discourse-cdn.com/inductiveautomation/original/1X/8e510869ae26fbd7ab76e1e731ceea2054ef109c.gif "Hrm") ---
**Author:** ![Pat](https://avatars.discourse-cdn.com/v4/letter/p/8491ac/32.png) [@Pat](https://forum.inductiveautomation.com/u/Pat)\ **Post date:** [May 23, 2013, 2:26am UTC](https://forum.inductiveautomation.com/t/query-problem-monthly-change-of-historical-db/6554/2 "2013-05-23T02:26:48Z")
You’re not going to get any meaningful accuracy by using the data from Ignition’s Historian tables. I would use a transaction group to log the flow at a given rate. Then once a day I would perform a daily volume calculation and write that to a table. Then query your daily\_volume table for your monthly/annual data. ---
**Author:** ![max7184](https://avatars.discourse-cdn.com/v4/letter/m/aeb1de/32.png) [@max7184](https://forum.inductiveautomation.com/u/max7184)\ **Post date:** [May 23, 2013, 2:57pm UTC](https://forum.inductiveautomation.com/t/query-problem-monthly-change-of-historical-db/6554/3 "2013-05-23T14:57:47Z")
it’s a possibility… but the problem is how can i use the historical tag without search the data in each table… isn’t there a tip to call the name of the tag and use it in all the historical db? ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [May 23, 2013, 9:18pm UTC](https://forum.inductiveautomation.com/t/query-problem-monthly-change-of-historical-db/6554/4 "2013-05-23T21:18:06Z")
I think Pat is right - you want to use transaction groups to story history that should be queried in this manner. I didn’t understand your most recent question. ---
**Author:** ![max7184](https://avatars.discourse-cdn.com/v4/letter/m/aeb1de/32.png) [@max7184](https://forum.inductiveautomation.com/u/max7184)\ **Post date:** [May 24, 2013, 3:15pm UTC](https://forum.inductiveautomation.com/t/query-problem-monthly-change-of-historical-db/6554/5 "2013-05-24T15:15:15Z")
the question is: how can I change this query `select (sum(my_avg)*3600) from ( SELECT avg(sqlt_data_1_2013_05.floatvalue) as my_avg,(from_unixtime(sqlt_data_1_2013_05.t_stamp/1000)) as my_data FROM ignition.sqlt_data_1_2013_05 where sqlt_data_1_2013_05.tagid = 4856 and from_unixtime(sqlt_data_1_2013_05.t_stamp/1000) <= (select current_timestamp) and from_unixtime(sqlt_data_1_2013_05.t_stamp/1000) >= date_sub(curdate(), interval 24 hour) group by hour(from_unixtime(sqlt_data_1_2013_05.t_stamp/1000)) ) as tbl1` to have the report, for example, from the last 1000 days to now? i’d like to use the name of the tag used in ignition, and not the number of the db index of the historical tag in the db… The problem is that ignition create this tables in the historicaldb: sqlt\_data\_1\_2013\_05 sqlt\_data\_1\_2013\_04 sqlt\_data\_1\_2013\_03 sqlt\_data\_1\_2013\_02 etc etc… i’d like to search in every table, calling directly the name of the historian tag ![:scratch:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/76d1620bec252bfac07050c889ff5a91dc8a9c25.gif ":scratch:") ![:scratch:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/76d1620bec252bfac07050c889ff5a91dc8a9c25.gif ":scratch:") ![:scratch:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/76d1620bec252bfac07050c889ff5a91dc8a9c25.gif ":scratch:") ![:scratch:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/76d1620bec252bfac07050c889ff5a91dc8a9c25.gif ":scratch:") ![:prayer:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e5a7e3b63facc151ea73e3b45db7bdb3d4bdc316.gif ":prayer:") ![:prayer:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e5a7e3b63facc151ea73e3b45db7bdb3d4bdc316.gif ":prayer:") ![:prayer:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e5a7e3b63facc151ea73e3b45db7bdb3d4bdc316.gif ":prayer:") ![:prayer:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e5a7e3b63facc151ea73e3b45db7bdb3d4bdc316.gif ":prayer:") …in street language… i’d like to use something similar to: select and sum all values from the tag TEST, in the last XXX days. and this function will works everytime, without use the name of the tables of the historical db… ---
**Author:** ![Colby.Clegg](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/colby.clegg/32/3282_2.png) [@Colby.Clegg](https://forum.inductiveautomation.com/u/Colby.Clegg)\ **Post date:** [May 24, 2013, 4:57pm UTC](https://forum.inductiveautomation.com/t/query-problem-monthly-change-of-historical-db/6554/6 "2013-05-24T16:57:36Z")
I’ll be the third person to say that this would be better done with a transaction group, where you could better control how the data was logged. If you must go forward with this, probably the easiest thing to do would be to turn off monthly partitioning, and have all of the data store to one table. Then, the next trick is going to be to join the data table to the sqlth\_te table, which maps tag paths (“tagpath”) to “id”, for example: ```auto select floatvalue from sqlt_1_data d, sqlth_te t where d.tagid=t.id and t.tagpath='path/to/tag' and d.t_stamp between unix_timestamp(date_sub(now(), interval 24 hour))*1000 and unix_timestamp(now())*1000 ``` Notice that while that isn’t the same query, I took the liberty of cleaning up a few elements of what you had. There’s no need to sub select the time, and for performance it’s much better to not modify the t\_stamp column in your where clause, because then the index on that column can’t be used. Anyhow, once again, I think a transaction group would be better, because then you could just do stuff like “select sum(mytag) from mydata” and be done. Given the way that history is stored (on change, not on an interval), I’m not even 100% sure you can get what you want out of the tag history tables. Regards, ---
**Author:** ![max7184](https://avatars.discourse-cdn.com/v4/letter/m/aeb1de/32.png) [@max7184](https://forum.inductiveautomation.com/u/max7184)\ **Post date:** [May 27, 2013, 4:04pm UTC](https://forum.inductiveautomation.com/t/query-problem-monthly-change-of-historical-db/6554/7 "2013-05-27T16:04:23Z")
oh, i’ll work with the ransaction groups sure, but the last question was for possible needs 🙂 thanks a lot to all ![:thumb_left:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e26afb081ddde6df016df6ee49159cf56aaca688.gif ":thumb\_left:") ![:thumb_right:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/2026e28c0c425034352c1b75807edf956adc75f4.gif ":thumb\_right:")