I am trying to display a graph and table that shows the average current of a motor for each day. I’m wanting to show this data over a month to see if the motor current is increasing. I created a SQL database that triggers the value every hour. I can show the data in a graph but there are so many peaks and valleys that you can’t get a good visual of the increase of current and that’s why I want an average. A SQL tag binding on the graph doesn’t give the option to display an average. How can I create a SQL query that finds the daily average of the motor current?
Assuming you are already historizing your current tag, I would probably use scripting to return this data rather than a SQL query. Something like this would likely get you in the ballpark:
paths = ['[default]Some_Device/Current'] endDate = system.date.midnight(system.date.now()) startDate = system.date.addDays(endDate, -30) system.tag.queryTagHistory(paths, startDate, endDate, aggregationMode = 'SimpleAverage', intervalHours = 24)
I suspect that you are going about this the wrong way. If you have a load that fluctuates then you would need to filter or average the readings more frequently than your OPC scan rate might allow. A better fix would be to install an energy meter and record the total kWh periodically. From that you can calculate the average power drawn per minute, hour, day, week, etc. much more accurately.
If you can’t do that then see what averaging you can do at source before collection by Ignition.