工厂可用性监视器的想法[已关闭]

Question

What I want to do is set up a page that can monitor the availability of a piece of plant, i.e. how many hours in a day was a JCB working? I'd like to have a single button that will change the status of the plant (easy) on a page but I'd like to track when the status changed and then be able to calculate the availability for a given period (the bit I'm stuck on).

I'm struggling to think of how I can structure the database and php script to allow me to do this... Any ideas/help greatly appreciated :)

Answer 1

You could structure your table with columns for JCB ID, start time, and end time like below

jcb_id    start_time               stop_time
1         2013-01-31 00:00:00      2013-01-31 06:19:00
1         2013-01-31 21:04:00      null
2         2013-01-31 08:00:45      2013-01-31 18:19:00

In the example above:

• jcb_id 1 is currently active because the stop_time is null.
• jcb_id 2 is down because there is no record with a null stop time.

When you click the button, the page will check to see if there is a record for the JCB with a null stop_time.

• select from the jcb table where jcb_id = ? and stop_time is null
• If a null stop time exists for the JCB,
  • update the record with NOW() - JCB is not working
• Else - there is no null stop time record for the JCB
  • insert a new record, leaving stop_time null - JCB is working

From there, you can run a query to calculate the time between the starts and stops to get up/downtime.