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.
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;
}