# Pump working hours

**URL:** <https://forum.inductiveautomation.com/t/pump-working-hours/43891>\
**Category:** Ignition\
**Created:** [February 26, 2021, 8:48pm UTC](https://forum.inductiveautomation.com/t/pump-working-hours/43891 "2021-02-26T20:48:47Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![zarkokv](https://avatars.discourse-cdn.com/v4/letter/z/73ab20/32.png) [@zarkokv](https://forum.inductiveautomation.com/u/zarkokv)\
**Post date:** [February 26, 2021, 8:48pm UTC](https://forum.inductiveautomation.com/t/pump-working-hours/43891/1 "2021-02-26T20:48:47Z")

</div>

Hello,  
I need to calculate working hours of water pump. I read more than ten similar topic but I could not to create valid calculation.  
I have one Boolean tag, named Pump, and historian on it. I use MySQL.  
In database I can find pump history table. When pump is active in table column “intvalue” value is 1 and “t\_stamp” value is timestamp, in unix\_timestamp format (13 number).  
In theory it should be easy query, unix\_timestamp(pump off) - unix\_timestamp(pump on)= period of pump activity in seconds. But, this is valid only for one time activity.  
I’m stuck with this, can someone help me with this?

---

<div class="post-metadata">

**Author:** ![mcgheeiv](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@mcgheeiv](https://forum.inductiveautomation.com/u/mcgheeiv)\
**Post date:** [February 28, 2021, 12:14pm UTC](https://forum.inductiveautomation.com/t/pump-working-hours/43891/2 "2021-02-28T12:14:07Z")

</div>

This video might help…[https://inductiveuniversity.com/videos/hour-and-event-meters/8.1](https://inductiveuniversity.com/videos/hour-and-event-meters/8.1)

---

<div class="post-metadata">

**Author:** ![zarkokv](https://avatars.discourse-cdn.com/v4/letter/z/73ab20/32.png) [@zarkokv](https://forum.inductiveautomation.com/u/zarkokv)\
**Post date:** [February 28, 2021, 12:46pm UTC](https://forum.inductiveautomation.com/t/pump-working-hours/43891/3 "2021-02-28T12:46:48Z")

</div>

Thank you @mcgheeiv for pointing me to video.  
Problem is that buying SQL Bridge Module license is over budget for my project. That is the reason why I choose path of MySQL query for calculating working hours of pump.

---

<div class="post-metadata">

**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 28, 2021, 1:00pm UTC](https://forum.inductiveautomation.com/t/pump-working-hours/43891/4 "2021-02-28T13:00:08Z")

</div>

If you can’t afford the SQL Bridge module, you can always create scripts that do the equivalent with gateway tag change events and/or timer events.

---

<div class="post-metadata">

**Author:** ![zarkokv](https://avatars.discourse-cdn.com/v4/letter/z/73ab20/32.png) [@zarkokv](https://forum.inductiveautomation.com/u/zarkokv)\
**Post date:** [February 28, 2021, 2:01pm UTC](https://forum.inductiveautomation.com/t/pump-working-hours/43891/5 "2021-02-28T14:01:48Z")

</div>

Hello @pturmel,  
Thank you for joining in.  
I had already try with tag event script where, on tag change, script insert timestamp in MySQL table. But, that is the same that already do historian 🙂  
Do you have some example script from which I could understand how to do that?

---

<div class="post-metadata">

**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 28, 2021, 2:47pm UTC](https://forum.inductiveautomation.com/t/pump-working-hours/43891/6 "2021-02-28T14:47:45Z")

</div>

> [@zarkokv](#):
>
> But, that is the same that already do historian

The historian add much indirection and, with partitioning, a variable list of tables to work with. A dedicated table can be simple, and with the use of lead()/lag() functions, easy to query for interval lengths. This post discusses the technique:

> [@Calculations based on relatime values](http://forum.inductiveautomation.com/t/calculations-based-on-relatime-values/10418/3):
>
> Adding to Al’s suggestion… When you then need to query the database for completed cycles, you can use your database’s window aggregate functions to give you the deltas. For example, in Postgres, it’d look something like this:Select t\_stamp, deltaweight From ( Select t\_stamp, weight - lead(weight) over (order by t\_stamp desc) as deltaweight From grouptable Order By t\_stamp Desc Limit 2) grouptabledelta Limit 1;Note that lead() and lag() are based on the row order involved. Since this q…

{ Note that MySQL can do window aggregates now. But I still recommend MariaDB or PostgreSQL. }

---

<div class="post-metadata">

**Author:** ![zarkokv](https://avatars.discourse-cdn.com/v4/letter/z/73ab20/32.png) [@zarkokv](https://forum.inductiveautomation.com/u/zarkokv)\
**Post date:** [February 28, 2021, 3:11pm UTC](https://forum.inductiveautomation.com/t/pump-working-hours/43891/7 "2021-02-28T15:11:04Z")

</div>

Thank you, I will try.

---

<div class="post-metadata">

**Author:** ![zarkokv](https://avatars.discourse-cdn.com/v4/letter/z/73ab20/32.png) [@zarkokv](https://forum.inductiveautomation.com/u/zarkokv)\
**Post date:** [March 1, 2021, 1:29pm UTC](https://forum.inductiveautomation.com/t/pump-working-hours/43891/8 "2021-03-01T13:29:14Z")

</div>

@pturmel thank you very much for help.  
I succeed to solve problem with dedicated table and creating scripts for tag change events.  
Best regards