How to calculate avg on time and off time of the machine?

Ignition
1

Hello friends,

I installed a sensor for monitoring AC status. It gives me a value of 1 or 0 when the AC is on or off. So, I log that value(0/1) in the database using the transaction group. I want to calculate the total working hours and non-working hours when the AC is on or off. Also, AC data is required to be reported.

How to calculate the working time and non-working time of AC?

Sample report format for reference.

I want the that report daily basis.

image
image490×519 4.84 KB

I want to log data at specific scheduled times. For example (21:00 PM to 8:00 AM)log time. At this point, the sensor data should be logged into the database.

So, how to log data using transaction group a by specific time?

Regards& Thanks,
Govind Suthar

2

Transaction groups have a schedule option. See below.

https://docs.inductiveautomation.com/display/DOC81/Understanding+Transaction+Groups#UnderstandingTransactionGroups-Schedule

There's an IU video link on that page and that might give you some ideas.

1 Like
3

Referencing this question:

If you are logging the AC every 20 minutes (as in your sample), then you can multiply the value count by 20 and have the same granularity.

image
image700×416 24.1 KB

select log_id,
       CASE WHEN value = 1 THEN 'ON' ELSE 'OFF' END AS status,
       COUNT(value)*20 as minutes
from my_table
group by value, log_id

image

4 Likes
4

Is it possibale without using transaction group?

5

Yes, it's possible, fairly simple and very powerful. See my posts in Durations for certain tag value.

1 Like
6

I took the value from the database. Data is not logged using tag history.

Can you give a query and how to implement?

7

If the log interval not fixed then how to work it?

8

What database are you using? MySQL / MariaDB / MS SQL, etc.?

1 Like
10

MySQL Databse

11

where to to run this script?

12

Use your database's lead() or lag() function to pull a value from the adjacent row for you to compute time deltas (right in the SQL), then sum the time deltas that correspond to on-time.

3 Likes
13

You mentioned report in your first post, are you using the reporting module? Or just a table on a view/window?

14

Yes, I use a report module

15

OK, so then that SQL query goes here: