I’m working on an energy metering project to learn Ignition and finish up with a useful application.
Question
Is it possible to view the underlying SQL for a Tag History binding (such as the one below) so that I can use it as a basis for developing my own query?
The tag history binding below returns the kWh meter readings for each hour.
Background
The result of the tag history binding is a table of meter readings. I need to modify the SQL query to return a table of the differences between rows.
Just note that the data tables get modified sometimes when things change in Ignition (like if tags get deleted and readded) and that the tables are also dependent on the time frames that you create when you create the history connection.
What are you trying to do? Is the data simply being displayed in a table?
You might look for a way to hide the columns that this binding creates, and add a new column that does the math.
Many databases can be configured to log the queries they are instructed to perform, like setting log_statement = all in the configuration for PostgreSQL.
You’ll want to do this in a test database, not a production system, of course. (-:
@Caleb: The data will be used for both tables (in reports) and charts.
@pturmel: I think I should avoid writing my own direct queries for the historian and rather let Ignition sort out the basic data extraction. It seems to sort out all the partitioned data tables, variable types and timestamps. See below.
@WKetcham: I think yours is the correct approach and was the conclusion I was coming to - let Ignition generate a dataset from the tag binding and then use Python to re-work the data. When you say “then write a property change script” I am a bit lost. What “property” is going to change and when?
@Anybody: I can’t find a worked example of manipulating a dataset and how to trigger it. Any pointers?
If you create a custom property on your table and then bind it to your tag history, when the data update on the custom property it will trigger the data change event on the table. If someone doesn’t beat me to the punch I will post a script example of the dataset manipulation in the morning.
If you get stuck with something odd, look at the view() function in my Simulation Aids module. It is a binding expression function that processes datasets into new datasets, with filtering, grouping, pivoting, and ordering. It uses python under the hood, and in debug mode, will show you the python it created from your pseudo-SQL.
WKetcham’s response gave me enough clues to make something work. For anyone trying to do the same (and there are quite a few posts on kWh readings) here’s my way in my first dabble into scripting.
Add a table component. This one will, eventually, be hidden from view. It will do the grunt work in retrieving the records from the historian.
Add a second table will be the manipulated data and will be filled by a Python script.
Add a DateRange tool and we’ll add a script to it to extend the end-date by a few seconds (a minute is fine for my application) to include one more reading after the range of interest. i.e., If I want to calculate kWh used each hour then I need to retrieve the record for 00:00 the following day so I can subtract the 23:00 reading from that to calculate the final hour’s usage.
Finally, any time the data in Table1 changes then run the script to calculate the hourly usage. Feed the output to Table2.
1 & 2. Create the tables
The one on the left, “kWh readings” is bound to the Tag History for the kWh of interest. Binding shown below.
Right-click the left table and add a custom property.
The “Diff table” on the right is blank for now.
3. The DateRange component
We need to do a trick with the date selector. Since I want the energy used per hour I need one extra reading to be returned from the historian - in this case the reading at 00:00 on the following day. We can do this by script. Right-click on the DateRange component and add the script.
# For calculation of differences between readings we need one extra reading.
# e.g. For hourly readings from 00:00 to 23:59:59 we need to add a few seconds
# to the end time to make sure we get the reading for the end of the last period
# at 00:00 the next day.
if event.propertyName == "event.source.endDate":
event.source.endDate = event.source.endDate + datetime.timedelta(minutes=1)
At this stage your left table should work and select the records for the period of interest including the one following record.
4. Calculate the row differences
Now Right-click on the left table and add the following script:
# Difference between dataset rows.
# This script takes the dataset from a table component and generates an array
# giving the timestamp and difference between rows.
if event.propertyName == "TableCustProp":
# Pull the dataset property from a table component
data = event.source.parent.getComponent("kWh readings").data
# Create an array for the output data.
rowsOut = []
# Loop through the dataset, perform the diff calculation and add to the array.
for row in range(data.rowCount - 1):
oneRow = [data.getValueAt(row, 0), data.getValueAt(row + 1, 1) - data.getValueAt(row, 1)]
rowsOut.append(oneRow)
# Create headers for the new table.
headers = ["Timestamp", "kWh"]
# Create the new dataset from the array.
dataOut = system.dataset.toDataSet(headers, rowsOut)
# Use our new dataset to fill in a Table
table = event.source.parent.getComponent("DiffTable")
table.data = dataOut
How it works*
Changing the date range causes an update on the left table.
This triggers the script which runs the calculation.
I have a little work to do to figure out if the end-date modification works on start-up. I may have to force that separately.
If you only want a days worth of data (midnight to midnight) then you might consider using a Calendar component (with Time Style set to Start of Day) in lieu of a Date Range. Instead of trying to explain everything in words and pictures, I have attached an example project to illustrate my suggestions.
Also from reading your original posts, I got the impression that you were really only after the hourly data so I included a second window to illustrate how you could do that. Just import the windows into your existing project and make the the tag history bindings (I included some notes on the screen for where to make the binding.
Based on your last post it looks like you are pretty close. I couldn’t figure out what “TableCustProp” was bound to, if it is bound to Data on it’s own table my example illustrates a slightly cleaner way to approach it.
All of this being said if you are wanting to create a report repeatedly with hourly snap shots of the total kWh then I suggest using a transaction group to log the data (refer to my original post). Because of the way the tag historian logs there may not be (an probably isn’t) a kWh record exactly on the hour so when you tell ignition to give you data on the hour it has to do math on the two logged data points that are on either size of the time you are requesting. What Ignition does is controlled by the Aggregation Mode (I will leave the discussion of the different aggregation modes alone for the moment) The point is the value Ignition returns may not be the exact value at that point in time, usually this isn’t a significant issue but occasionally depending on how the data changes and how you have the historian is configured it can throw your values off a bit. Really it come done to what level of accuracy you need. If it is informational - no worries, if it is revenue metering - could be a concern.
I will typically use both strategies for logging data. I use the tag historian to log changes rapidly for trending and troubleshooting. Then I use transaction groups to log data on specific time intervals (but much less frequently that tag historian) to create my usage reports and the like. This has one other advantage (at least for the types of systems I work on), typically the data used to create the reports needs to be stored indefinitely but the minutia details for maybe only a year for troubleshooting. So I can configure the historian to purge old records to keep the size of my database in control. For smaller systems this probably isn’t an issue but if you are logging 4,000 tags but only using 100 of them for reports it could be beneficial. Just a suggestion. kWh Example.proj (41.1 KB)
