Page 1 of 1

Find the difference

Posted: Wed Jun 04, 2008 11:22 pm
by sandy1028

Code: Select all

 
 
Name     Timestamp              Status
|A | 2008-06-01 06:20:01 |             1 |
| A| 2008-06-01 06:30:01 |            1 |
| A | 2008-06-01 06:40:01 |           1 |
| A | 2008-06-01 06:50:01 |           1 |
| A | 2008-06-01 07:00:01 |           1 |
| A | 2008-06-01 07:10:01 |           0 |           
| A | 2008-06-01 07:20:01 |           0 |
| A | 2008-06-01 07:30:01 |           1 |
| A | 2008-06-01 07:40:01 |             1 |
| A | 2008-06-01 07:50:01 |           1 |
| A | 2008-06-01 08:00:01 |           1 |
| A | 2008-06-01 08:10:01 |           0 |
| A | 2008-06-01 08:20:01 |           0 |
 
 
How to calculate timedifference when status changes from 0 to 1.

In the above record at timestamp 2008-06-01 07:10:01 to 2008-06-01 07:30:01 difference is 20 min.

And again status is 0 at 2008-06-01 08:10:01 and 2008-06-01 08:20:01
And find the difference

Strtotime() is used.

How to loop through these records to check the difference.

Re: Find the difference

Posted: Thu Jun 05, 2008 12:34 am
by Christopher
Select the Timestamp field as a Unix timestamp with a function. In MySQL it would be something like:

SELECT UNIX_TIMESTAMP(Timestamp) AS mytimestamp

Re: Find the difference

Posted: Thu Jun 05, 2008 12:37 am
by sandy1028
After fetching the records,

I tried using this code but I am not able to get the proper result.
The data is pushed into records even if status changes from 1 to 0.

I need only when status changes from 0 to 1 and another condition is when status doesnt change to 1.

I am finding it too difficult

Code: Select all

 
$currentStatus = $result[status'];
    $currentTime = $result['timestamp'];
    $previousStatus = $previousStatus == '' ? $currentStatus : $previousStatus;
    $previoustime = $previoustime == '' ? $currentTime : $previoustime;
 
    if($previousStatus == $currentStatus)
    {
                $previousStatus=$currentStatus;
                continue;
                }
        $d = strtotime($currentTime) - strtotime($previoustime);
         array_push($records,"$result[2]#$ip[0]#$result[2]#$previoustime#$result[1]#$d#$_");
    $previousStatus = $currentStatus;
        $previoustime=$currentTime;
}
 
 

Re: Find the difference

Posted: Thu Jun 05, 2008 2:37 am
by [UW] Jake
I had to deal with this recently:

Code: Select all

 
 
$time1 = "2008-06-01 08:00:01";
$time2 = "2008-06-01 08:10:01";
 
$seconds = abs(strtotime($time1) - strtotime($time2));
$minutes = floor($seconds/60);
 
//To compare those to the current time, use
$time3 = date("Y-m-d H:i:m");
 
//for example
$seconds = abs(strtotime($time3) - strtotime($time1));
 

Re: Find the difference

Posted: Thu Jun 05, 2008 3:33 am
by sandy1028
The problem is with finding the status change.

I have to calculate difference only when status chages from 0 to 1. From when status was 0.


If status doesnot change to 1 then calcualate the time from first row till last.
How to store the previous status and previous time

Re: Find the difference

Posted: Thu Jun 05, 2008 4:23 am
by Scrumpy.Gums
If I understand you correctly, you want to do something like:

Code: Select all

 
$gotZero = false;
while ($result = mysql_fetch_array($qry))
{
    if ($result['status'] == 0) 
    {
        $gotZero = true;
        // Temporarily store the time
        $zeroTime = $result['timestamp'];
    }
    else
    {
        if ($gotZero === true)
        {
            // Calculate difference
        }
        $gotZero = false;
    }
}
 
$gotZero is a boolean flag to indicate if the previous time had a 0 status. The above code (with a few modifications ofc) will calculate the difference when a 0 status is followed by a 1 status.