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.