# Please help calculating uptime

**URL:** <https://forum.inductiveautomation.com/t/please-help-calculating-uptime/5768>\
**Category:** Ignition\
**Created:** [January 31, 2013, 1:17am UTC](https://forum.inductiveautomation.com/t/please-help-calculating-uptime/5768 "2013-01-31T01:17:45Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![seancourtney](https://avatars.discourse-cdn.com/v4/letter/s/43a26b/32.png) [@seancourtney](https://forum.inductiveautomation.com/u/seancourtney)\
**Post date:** [January 31, 2013, 1:17am UTC](https://forum.inductiveautomation.com/t/please-help-calculating-uptime/5768/1 "2013-01-31T01:17:45Z")

</div>

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

thanks

---

<div class="post-metadata">

**Author:** ![AlThePal](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/althepal/32/3603_2.png) [@AlThePal](https://forum.inductiveautomation.com/u/AlThePal)\
**Post date:** [January 31, 2013, 2:11pm UTC](https://forum.inductiveautomation.com/t/please-help-calculating-uptime/5768/2 "2013-01-31T14:11:25Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Pat](https://avatars.discourse-cdn.com/v4/letter/p/8491ac/32.png) [@Pat](https://forum.inductiveautomation.com/u/Pat)\
**Post date:** [January 31, 2013, 2:48pm UTC](https://forum.inductiveautomation.com/t/please-help-calculating-uptime/5768/3 "2013-01-31T14:48:15Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![seancourtney](https://avatars.discourse-cdn.com/v4/letter/s/43a26b/32.png) [@seancourtney](https://forum.inductiveautomation.com/u/seancourtney)\
**Post date:** [January 31, 2013, 3:45pm UTC](https://forum.inductiveautomation.com/t/please-help-calculating-uptime/5768/4 "2013-01-31T15:45:03Z")

</div>

[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??

sean

---

<div class="post-metadata">

**Author:** ![AlThePal](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/althepal/32/3603_2.png) [@AlThePal](https://forum.inductiveautomation.com/u/AlThePal)\
**Post date:** [January 31, 2013, 3:48pm UTC](https://forum.inductiveautomation.com/t/please-help-calculating-uptime/5768/5 "2013-01-31T15:48:38Z")

</div>

Hi Sean,

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.