Finding Timestamp Difference

sandy1028

hi,

I have the fields like this
Name| status|timestamp

The status can be 0 or 1.

I have to find the timestamp difference between the status when it changes from 0 to 1.

here I have to find the difference of timestamp

master | 2008-03-24 09:10:02 | 0 |
| cluster | 2008-03-24 09:20:01 | 0 |
| master| 2008-03-24 09:20:01 | 1 |
| cluster | 2008-03-24 09:30:01 | 0 |
| master | 2008-03-24 09:30:01 | 1 |
| cluster | 2008-03-24 09:40:01 | 0 |
| cluster | 2008-03-24 09:50:02 | 0 |
| cluster | 2008-03-24 10:00:01 | 1 |
| cluster | 2008-03-24 10:10:01 | 0 |
| cluster | 2008-03-24 10:20:01 | 0 |
| cluster | 2008-03-24 10:30:01 | 0 |
| cluster | 2008-03-24 10:40:01 | 1 |
| cluster | 2008-03-24 10:50:01 | 0 |
| cluster | 2008-03-24 11:00:01 | 0 |
| cluster | 2008-03-24 11:10:01 | 0 |
| cluster | 2008-03-24 11:20:02 | 0 |
| cluster | 2008-03-24 11:30:02 | 1 |
| cluster | 2008-03-24 11:40:01 | 0 |
| cluster | 2008-03-24 11:50:02 | 0 |
| cluster | 2008-03-24 12:00:01 | 0 |
| master | 2008-03-24 12:00:01 | 0 |
| cluster | 2008-03-24 12:10:01 | 0 |
| master | 2008-03-24 12:10:01 | 0|
| cluster | 2008-03-24 12:20:01 | 0 |
| master | 2008-03-24 12:20:01 | 1 |

This is the result got from database.

When I print the difference I get the result for all the rows. I have to get only when status changes. For example the timestamp is at 2008-03-24 12:00:01 is 0 and name is master I should not calculate the difference of 2008-03-24 12:10:01 .
I have to directly calculate the differnce from 2008-03-24 12:00:01 to 2008-03-24 12:20:01 .

How to avoid the in between rows which has the status as 0.

Please help me with this

niroshan

well what you can do is get the last 2 records filtered by name and check if the status has changed. Here is an example,

<?php

//get the last two records
$sql = "SELECT timestamp,status FROM datedifference WHERE name='master' ORDER BY timestamp DESC LIMIT 0,2";

$row = mysql_query($sql);
while($result = mysql_fetch_array($row))
{
	$statusArr[] = $result['status'];
	$timeArr[] = $result['timestamp'];
}

if($statusArr[0] <> $statusArr[1])
{

//now we know the status has change so get the time diffrence.

}
?>

Hope this would helps.

Best regards,
Niroshan

sandy1028

Hi,

It is not for last two records. I want it for all the status when it is changed

niroshan

Try this,

$sql = "SELECT status FROM datedifference WHERE name='master' ORDER BY timestamp ";
$row = mysql_query($sql);

$previousStatus = '';
$currentStatus = '';
while($result = mysql_fetch_array($row))
{
	$currentStatus = $result['status'];	
	$previousStatus = $previousStatus == '' ? $currentStatus : $previousStatus;

if($previousStatus <> $currentStatus)
{
	//status changed ......
}

$previousStatus = $currentStatus;
}

Regards,
Niroshan

sandy1028

How to get the previousstatus.

previousstatus==currentstattus only.. How to get it

niroshan

What do u mean by how to get previousStatus?

I`m assigning the values to previousStatus inside the while loop. for example in the whileloop,
for the first record previousStatus will be the same as currentStatus because that was the first record. then in the second time the loop starts i assign the previous value as currentStatus and currentStatus will have the second records status value.

here is an example,

Records Status

1 ----------> 0
2 ----------> 1
3 ---------->0

while() //loop goes 3 times
{
    //first time
    $currentStatus =0;
    $previousStatus=0;

 //STATUS NOT CHANGED


//second time
$currentStatus =1;
$previousStatus=0;

 //STATUS CHANGED

//third time
$currentStatus =0;
$previousStatus=1;

//STATUS CHANGED
}

Hope this is clear.

sandy1028

Hi,

Thanks for the reply.

$previousStatus = '';
$currentStatus = '';
$previoustime='';
$currentTime='';
//$storedTime = '';

$currentStatus = $result['status'];
$currentTime = $result['timestamp'];
$previousStatus = $previousStatus == '' ? $currentStatus : $previousStatus;
$previoustime = $previoustime == '' ? $currentTime : $previoustime;

if($previousStatus <> $currentStatus)
{
    $d = strtotime($currentTime) - strtotime($previoustime);
    array_push($records,"$result[2]#$ip[0]#$result[2]#$previoustime#$result[1]#$d");

    }
$previousStatus = $currentStatus;
}

}

Here if status is 0 and status is not changed the name is not printed.
The name and entire row is pushed into array only if the status is changed.How to get the value if status is 0 through out the value in database.

The previous timestamp is the fetched as the first timestamp of curent date in the table. How to tak the previous timestamp

niroshan

Hi,
Sorry about the delayed reply but im sorry im not quite sure about the two questions your asking.

How to get the value if status is 0 through out the value in database.

Do you want to fetch all the records which is status is equal to 0?

The previous timestamp is the fetched as the first timestamp of curent date in the table. How to tak the previous timestamp

Here are you talking about the first record of the table? if you can be more specific then i may able to help.

Thanks,
Niroshan

sandy1028

The above code fetches the status which is 0 and status 1.
But previoustime and previousstatus field tells only when the status changes from 0 to 1 or 1 to 0.

Sometimes the status is not equal to 1 it is 0 throughout. But the condition which is used doesnt calculate the status which is 0 throughout.

In such condition I have to calculate timestamp from current timestamp-previous timestamp(i.e, when it was 0)

niroshan wrote:
Hi,
Sorry about the delayed reply but im sorry im not quite sure about the two questions your asking.

Do you want to fetch all the records which is status is equal to 0?

Here are you talking about the first record of the table? if you can be more specific then i may able to help.

Thanks,
Niroshan

niroshan

Hi,

I`m bit confuse now. your saying you want to calculate all the time changes, regardless of the status???😕 then whats the point of having a status??

sandy1028

Not regardless of status. Only if all status is 0 then how to calculate the diffference from current timestamp and from when status was 0

niroshan

I hope I got you right this time 🙂

$previousStatus = '';
$currentStatus = '';

$previousDate = '';
$currentDate = '';

while($result = mysql_fetch_array($row))
{
	$currentStatus = $result['timeStatus'];	//get the current record status
	$currentDate = $result['timest'];	//get the current record time

//if previous status is empty we assign previous status to current status (this happens only on first record) else we keep the previous value as it is (this happen all the time except for the first record)
$previousStatus = $previousStatus == '' ? $currentStatus : $previousStatus;
//do same for the time as well
$previousDate = $previousDate == '' ? $currentDate : $previousDate; 

if($currentStatus == $previousStatus) //check if the status has changed
{
	$previousStatus = $currentStatus;
	$previousDate = $currentDate;
	continue; //this will skip the below part so we dont calculate the time different because the previous and current status are same
}

//Get the date diff
$dateDiff = strtotime($currentDate) - strtotime($previousDate);


$previousDate = $currentDate; 
$previousStatus = $currentStatus;
}

according to your dummy data ,

master | 2008-03-24 09:10:02 | 0 | //skip
| master| 2008-03-24 09:20:01 | 1 | //calculate the time diff
| master | 2008-03-24 09:30:01 | 1 |//skip
| master | 2008-03-24 12:00:01 | 0 |//calculate the time diff
| master | 2008-03-24 12:10:01 | 0|//skip
| master | 2008-03-24 12:20:01 | 1 |//calculate the time diff

best regards,
Niroshan

sandy1028

To calculate the timediffernence at 2008-03-24 12:20:01 the previous time is taken as 2008-03-24 09:10:02 not the 2008-03-24 12:00:01

How to avoid it