PHP Calculation 'downtime'

Question

I've made an program in PHP that stores in a MySQL database when it's working. The table that shows whether the program is working or not looks like this:

Table_name: downtime
Working(int)    Time(datetime)
1               2011-05-06 15:18:55
0               2011-05-06 15:20:21
1               2011-05-06 15:20:24
0               2011-05-06 16:05:13
1               2011-05-06 16:05:15
0               2011-05-06 16:27:59
1               2011-05-06 16:28:01
0               2011-05-06 16:37:35
1               2011-05-06 16:37:37

Now I would like to retrieve the 'downtime' between to dates. Say date 1 is 2011-05-06 15:20:24 and date 2 is 2011-05-06 16:28:15, then I would like the script to return the sum of the differences between the 1's and 0's in that period. How can I make this?

Important to know if when date 1 for example is 2011-05-06 15:20:22, then there are two extra seconds downtime. It's hard to think of a (almost) perfect solution.

Answer 1

This code should work, I've tested it with your example data:

// Example data
// Note: assumes $data is sorted by date already
$data = array(
    "2011-05-06 15:18:55"=>1,
    "2011-05-06 15:20:21"=>0,
    "2011-05-06 15:20:24"=>0,
    "2011-05-06 16:05:13"=>0,
    "2011-05-06 16:05:15"=>0,
    "2011-05-06 16:27:59"=>1,
    "2011-05-06 16:28:01"=>0,
    "2011-05-06 16:37:35"=>0,
    "2011-05-06 16:37:37"=>1
    );

$startTime = strtotime("2011-05-06 15:18:55");
$endTime = strtotime("2011-05-06 16:28:01");

$totalUptime = 0;    
$lastUptime = 0;

foreach ($data as $dateString => $isWorking)
{
    $timestamp = strtotime($dateString);

    if ($lastWorking && $lastTimestamp)
    {
        $allowedStart = $lastTimestamp;
        if ($allowedStart<$startTime)
            $allowedStart = $startTime;

        $allowedEnd = $timestamp;
        if ($allowedEnd>$endTime)
            $allowedEnd = $endTime;

        $diff = $allowedEnd-$allowedStart;

        if ($diff>0)
        {
            $totalUptime += $diff;
        }
    }

    if ($timestamp>=$endTime)
    {
        break;
    }

    $lastTimestamp = $timestamp;    
    $lastWorking = $isWorking;
}

print "Total uptime: ".$totalUptime." seconds";

You just need to populate $data with your own records from the database.

Answer 2

Go through all the rows converting the timestamps to unix epochs with strtotime(), then just find the difference between them, and add it up to get total down time.

Answer 3

The solution I am using now is the following:

    $objects = Get("table","`date` BETWEEN '".$date_start."' AND '".$date_end."'");   

    $prev = new stdClass();
    $prev->new = 1;
    $prev->date = $date_start;

    $next = new stdClass();
    $next->new = 0;
    $next->date = $date_end;
    $objects = array_merge(array($prev),$objects,array($next));

    $uptime = 0;
    $downtime = 0;
    $prevtime = false;
    foreach($objects as $object)
    {
        $newtime = strtotime($object->date);
        if($prevtime && $object->new == 1)
        {
            $downtime += $newtime - $prevtime;
        }
        elseif($prevtime && $object->new == 0)
        {
            $uptime += $newtime - $prevtime;
        }
        $prevtime = $newtime;
    }     

Get(); is my function to communicate to the database: it returns objects (records) found by the query, replace it with the following if you want to test/use this code:

$objects = array();
$result = mysql_query("SELECT * FROM table WHERE `date` BETWEEN '".$date_start."' AND '".$date_end."'");
while($object = mysql_fetch_object($result))
{
    $objects[] = $object;    
}

Answer 4

select timediff(
(
    select `time`
    from downtime
    where working = 1
    and `time` > t1.`time`
    order by `time` asc
    limit 1
),
t1.`time`
)
from downtime t1
where t1.working = 0

This should give you several rows, one for each period of downtime. Each row shows a time difference and you would still have to add those rows together to find the actual downtime. I'm not sure what happens when you use the sum() function of MySQL on time differences, it probably just returns seconds. You could limit the time field as desired, though I have not tested what happens when start time or end time is in a downtime period.

Answer 5

Edit: I misunderstood the question, ignore this answer

The answer depends very much on how you've stored that data in MySQL. You'll most likely need a query like:

SELECT COUNT(*) FROM table WHERE working='1' AND DATE>='startDate' AND DATE<='endDate';

Which will give you the number of '1's in a given range.