Pump working hours

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?

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

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.

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.

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 :slight_smile:
Do you have some example script from which I could understand how to do that?

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:

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

1 Like

Thank you, I will try.

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

1 Like