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
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.
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?
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.
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:
i’d like to search in every table, calling directly the name of the historian tag
…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…
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:
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.
