Hello,
I’ve been trying to create 24 hour average data from our current average shift data. I’ve been trying to do this by creating tags for every hour of the day and then they will update throughout each hour. One problem already with this method is that it will only be showing the past 23 hours once the next hour starts due to the old tag being overwritten. My current setup is in an expression memory tag I have it updating every minute, and then a tag event value change to affect all of my workcenters. I then read my hourly data and set that to a variable, but must subtract the previous hourly data in a shift from the overall shift data. This gets me the error TypeError: unsupported operand type(s) for -: ‘int’ and ‘com.inductiveautomation.ignition.common.sqltags.BasicTagValue’. Does anyone know how to fix this error or have a better method? If I need to explain this clearer please let me know. Thanks!
You’re asking tags to do something that is best done by your database, typically with the LAG() window aggregate function. Use a transaction group to record your counter any any related data at a steady pace. I recommend the schedule option that starts at midnight and runs every minute, keeping the records close to the start of each minute. Then you would use a query like the following (PostgreSQL syntax, others have different date truncation methods):
SELECT h, qty
FROM (
SELECT date_trunc('hour', t_stamp) as h, sum(delta) as qty
FROM (
SELECT t_stamp, mycount - LAG(mycount) OVER (ORDER BY t_stamp) AS delta
FROM mytable
WHERE t_stamp > (current_timestamp - INTERVAL '26 hours')
) deltas
GROUP BY date_trunc('hour', t_stamp)
)
WHERE h > (current_timestamp - INTERVAL '25 hours')
ORDER BY h
Note that the larger time interval on the inner query is deliberate – it makes sure the first row through the LAG() function, which will have a NULL, gets dropped in the final result.