I have 1 tag that toggles its value between 1 and 0 , depending on the status of a motor (on/off)
i have this tag to store it’s history
i have a transaction group set up so that when this tag changes from a 1 to a 0 an entry is put into my database with a timestamp and a simple auto-increment as an entry number. AND when it changes back to a 1 again.
there is a status chart that easily shows realtime 1’s and 0’s for the office personel to see how this particular machine is doing on any given day, there is also a historical chart showing similar information with the ability for a date range to be selected to view the status of this device.
What i am being asked to do is:
total the time for the last 24 hours when the status was a 1
if i use the dataset for the historical status chart i will need to find a way to add thousands of timestamps , since every 1000 ms an entry is entered in the dataset
if i use the transaction group i think i would need to use datediff to calculate the time difference between 2 entries , then add up all of the differences in time to get the total uptime for a given period.
I am still a newbie and am having a difficult time figuring this one out, and make it displayed as easy for me and the user.
any ideas anyone??? if screenshots would help, i can do that too
I reckon the following script will do what you want:[code]#Use Java Calendar because it gets initialised to the current time and makes it easy to add/subtract time.
from java.util import Calendar
data = system.db.runQuery("""
SELECT t_stamp,
logging_trigger
FROM logging_test
ORDER BY t_stamp ASC
“”")
calendar = Calendar.getInstance() #Take a note of the current time
now = calendar.getTime() #Work out the start time 24 hours ago.
calendar.add(Calendar.HOUR, -24)
onTime = calendar.getTime() #Note - convert the Calendars to Date objects using getTime() because Ignition returns SQL dates as Date objects.
total = 0
for row in data: #Take a note of the on time.
if row[“logging_trigger”] == 1:
onTime = row[“t_stamp”] #For each off time, calculate the time since the last on time.
else:
offTime = row[“t_stamp”] #Date objects hold the number of milliseconds, so divide by 1000 to get seconds.
difference = (offTime.getTime() - onTime.getTime()) / 1000
total += difference #Reset the last on time.
onTime = None #If the state is currently on, add the period from the on time until the current time.
if onTime is not None:
extra = (now.getTime() - onTime.getTime()) / 1000
total += extra
system.tag.write(“uptime”, total)[/code]This totals up the time the value of the tag was 1 in the last 24 hours, writing the number of seconds to a SQLTag. It also takes account of the cases when the first record in the period was a 0 (the motor was already running) and the last record was a 1 (the motor was left running). I put this in a Gateway event script running every few seconds. If you didn’t want the value in a SQLTag you could put the script in a script module and call it using runScript from an object property’s expression.
Create a transaction group with an hour meter group item based on your tag. Create a second transaction group to write the value of the hour meter to a table at 12:01AM. Your daily runtime = (today’s hour meter value) - (yesterday’s hour meter value).
You can just query the table to find the daily runtime, but I actually create a 3rd transaction group that grabs the daily runtime value in a query at 12:05AM and then writes that value to a separate daily table. So my systems will have 3 tables hour_meters, daily_hour_meters, daily_runtimes.
[quote=“AlThePal”]I reckon the following script will do what you want:[code]#Use Java Calendar because it gets initialised to the current time and makes it easy to add/subtract time.
from java.util import Calendar
data = system.db.runQuery("""
SELECT t_stamp,
logging_trigger
FROM logging_test
ORDER BY t_stamp ASC
“”")
calendar = Calendar.getInstance() #Take a note of the current time
now = calendar.getTime() #Work out the start time 24 hours ago.
calendar.add(Calendar.HOUR, -24)
onTime = calendar.getTime() #Note - convert the Calendars to Date objects using getTime() because Ignition returns SQL dates as Date objects.
total = 0
for row in data: #Take a note of the on time.
if row[“logging_trigger”] == 1:
onTime = row[“t_stamp”] #For each off time, calculate the time since the last on time.
else:
offTime = row[“t_stamp”] #Date objects hold the number of milliseconds, so divide by 1000 to get seconds.
difference = (offTime.getTime() - onTime.getTime()) / 1000
total += difference #Reset the last on time.
onTime = None #If the state is currently on, add the period from the on time until the current time.
if onTime is not None:
extra = (now.getTime() - onTime.getTime()) / 1000
total += extra
system.tag.write(“uptime”, total)[/code]This totals up the time the value of the tag was 1 in the last 24 hours, writing the number of seconds to a SQLTag. It also takes account of the cases when the first record in the period was a 0 (the motor was already running) and the last record was a 1 (the motor was left running). I put this in a Gateway event script running every few seconds. If you didn’t want the value in a SQLTag you could put the script in a script module and call it using runScript from an object property’s expression.
Let me know how you get on with it.[/quote]
Thanks AlThePal, Just so i am sure i understand:
make an expression tag
logging_trigger is the tag that i am watching toggle between 1 and 0
Logging_test is the table being used by my transaction group to write on and off cycles with the timestamps
and i need to make 1 more memory tag for the expression tag to write to??
You can’t access an external script from an expression SQLTag, so I put the script into a Gateway event script, running every few seconds. This will update the memory SQLTag with the value. All your other assumptions are correct.