# How to calculate avg on time and off time of the machine? **URL:** \ **Category:** Ignition\ **Tags:** ignition81\ **Created:** [February 6, 2024, 6:52am UTC](https://forum.inductiveautomation.com/t/how-to-calculate-avg-on-time-and-off-time-of-the-machine/85272 "2024-02-06T06:52:57Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![Govind\_Suthar1](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@Govind\_Suthar1](https://forum.inductiveautomation.com/u/Govind_Suthar1)\ **Post date:** [February 6, 2024, 6:52am UTC](https://forum.inductiveautomation.com/t/how-to-calculate-avg-on-time-and-off-time-of-the-machine/85272/1 "2024-02-06T06:52:57Z")
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/e/de4ff4b8f535836ff3c63bd9883e2e94e4057291.png) 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 ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [February 6, 2024, 10:39am UTC](https://forum.inductiveautomation.com/t/how-to-calculate-avg-on-time-and-off-time-of-the-machine/85272/2 "2024-02-06T10:39:32Z")
Transaction groups have a schedule option. See below. [https://docs.inductiveautomation.com/display/DOC81/Understanding+Transaction+Groups#UnderstandingTransactionGroups-Schedule](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. ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [February 6, 2024, 12:28pm UTC](https://forum.inductiveautomation.com/t/how-to-calculate-avg-on-time-and-off-time-of-the-machine/85272/3 "2024-02-06T12:28:00Z")
Referencing this question: > [@Govind\_Suthar1](#): > > How to calculate the working time and non-working time of AC? 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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/4/34688c7bef447187a9fa2e4c8797dfe318aa5591.png) ```sql 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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/5/c585b963e035f41afe0fe78e9d50ed5d0896f0ea.png) ---
**Author:** ![Govind\_Suthar1](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@Govind\_Suthar1](https://forum.inductiveautomation.com/u/Govind_Suthar1)\ **Post date:** [February 7, 2024, 9:57am UTC](https://forum.inductiveautomation.com/t/how-to-calculate-avg-on-time-and-off-time-of-the-machine/85272/4 "2024-02-07T09:57:08Z")
> [@Govind\_Suthar1](#): > > s me a value of 1 or 0 when the AC is on or off. So, I lo Is it possibale without using transaction group? ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [February 7, 2024, 10:05am UTC](https://forum.inductiveautomation.com/t/how-to-calculate-avg-on-time-and-off-time-of-the-machine/85272/5 "2024-02-07T10:05:21Z")
Yes, it's possible, fairly simple and very powerful. See my posts in [Durations for certain tag value](https://forum.inductiveautomation.com/t/durations-for-certain-tag-value/83586). ---
**Author:** ![Govind\_Suthar1](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@Govind\_Suthar1](https://forum.inductiveautomation.com/u/Govind_Suthar1)\ **Post date:** [February 8, 2024, 9:34am UTC](https://forum.inductiveautomation.com/t/how-to-calculate-avg-on-time-and-off-time-of-the-machine/85272/6 "2024-02-08T09:34:54Z")
I took the value from the database. Data is not logged using tag history. Can you give a query and how to implement? ---
**Author:** ![Govind\_Suthar1](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@Govind\_Suthar1](https://forum.inductiveautomation.com/u/Govind_Suthar1)\ **Post date:** [February 8, 2024, 9:47am UTC](https://forum.inductiveautomation.com/t/how-to-calculate-avg-on-time-and-off-time-of-the-machine/85272/7 "2024-02-08T09:47:06Z")
> [@JordanCClark](#): > > ```python > 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 > > ``` If the log interval not fixed then how to work it? ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [February 8, 2024, 9:49am UTC](https://forum.inductiveautomation.com/t/how-to-calculate-avg-on-time-and-off-time-of-the-machine/85272/8 "2024-02-08T09:49:19Z")
What database are you using? MySQL / MariaDB / MS SQL, etc.? ---
**Author:** ![Govind\_Suthar1](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@Govind\_Suthar1](https://forum.inductiveautomation.com/u/Govind_Suthar1)\ **Post date:** [February 8, 2024, 9:57am UTC](https://forum.inductiveautomation.com/t/how-to-calculate-avg-on-time-and-off-time-of-the-machine/85272/10 "2024-02-08T09:57:48Z")
MySQL Databse ---
**Author:** ![Govind\_Suthar1](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@Govind\_Suthar1](https://forum.inductiveautomation.com/u/Govind_Suthar1)\ **Post date:** [February 8, 2024, 10:33am UTC](https://forum.inductiveautomation.com/t/how-to-calculate-avg-on-time-and-off-time-of-the-machine/85272/11 "2024-02-08T10:33:12Z")
> [@JordanCClark](#): > > ```python > 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 > > ``` where to to run this script? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [February 8, 2024, 12:10pm UTC](https://forum.inductiveautomation.com/t/how-to-calculate-avg-on-time-and-off-time-of-the-machine/85272/12 "2024-02-08T12:10:39Z")
> [@Govind\_Suthar1](#): > > If the log interval not fixed then how to work it? 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. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [February 8, 2024, 1:53pm UTC](https://forum.inductiveautomation.com/t/how-to-calculate-avg-on-time-and-off-time-of-the-machine/85272/13 "2024-02-08T13:53:02Z")
You mentioned report in your first post, are you using the reporting module? Or just a table on a view/window? ---
**Author:** ![Govind\_Suthar1](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@Govind\_Suthar1](https://forum.inductiveautomation.com/u/Govind_Suthar1)\ **Post date:** [February 9, 2024, 4:29am UTC](https://forum.inductiveautomation.com/t/how-to-calculate-avg-on-time-and-off-time-of-the-machine/85272/14 "2024-02-09T04:29:43Z")
Yes, I use a report module ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [February 9, 2024, 2:39pm UTC](https://forum.inductiveautomation.com/t/how-to-calculate-avg-on-time-and-off-time-of-the-machine/85272/15 "2024-02-09T14:39:20Z")
OK, so then that SQL query goes here: > **[Report Data Video at Inductive University](https://www.inductiveuniversity.com/videos/report-data/7.9)** > > The Data Panel is where your reports will pull in their data.