Timedifference on different rows.

[Ryflex]

Hi all,

 

I'm trying to make a piece of PHP that calculates the hours you can put down on your timesheet. Do do this I need to get al entries in the DB where the time between the rows is more than half an hour (I have a time field) and then count them.

Example of the table

Call1 | 07:12:10

Call2 | 07:15:25

Call3 | 08:00:38

 

The output has to be 2 because the times between Call1 and Call2 is less than half an hour but the difference between Call2 and Call3 is more than half an hour.

 

My question to you is: Is this possible and if so can anyone give me some hints on how to get the script to work this way.

 

Thanks all

 

Greetz Ryflex

[sunfighter]

I am treating your time information as a string. The return when it is greater then an hour is in decimal form, if you want it in the 1: 20 format you will have to do that.

 

<?php
function differate($row_1, $row_2)
{
$answer = number_format((diffine($row_2) - diffine($row_1))/60);
if ($answer < 30)
	return 'Less then half hour';
if ($answer > 60)
	return $answer/60;
}

function diffine($time)
{
$mine = explode(':', $time);
$minetotal1 = $mine[0] * 3600 + $mine[1] * 60 + $mine[2];
return $minetotal1;
}

// The given or form your query
$Call1 ='07 : 12 : 10';
$Call2 = '07 : 15 : 25';
$Call3 = '09 : 00 : 38';

// Your output
echo 'first difference is '. differate($Call1, $Call2).'<br>';
echo 'second difference is '. differate($Call2, $Call3).'<br>';

?>

[fenway]

Differences between rows requires a join.

[Ryflex]

Hi all,

 

Well I talked to our database specialist and he helped me format this code. Sadly he's off to home now and the code doesn't work.

//reset count and compare
  $count 	= 0;
  $compare	= -100;
  
  //Get data from DB
  $dataqry	= mysql_query("SELECT * FROM `Standby` WHERE `maand` = '$maand' && `jaar` = '$jaar' ORDER BY jaar DESC, maand DESC, dag DESC, tijd DESC");
  if(!$dataqry) {
die("Storingen kunnen niet gevonden worden.");
  }
  while($row = mysql_fetch_assoc($dataqry)) {
  //$count ++;
    if(strtotime($row['tijd']) - strtotime($compare) > strtotime("00:30:00")) {
  $count++;
  echo $compare."<BR>".$row['tijd']."<BR>";
  $compare	= $row['tijd'];
}
  }

 

Is there anyone who can help me get it right

 

Thanks

Ryflex

[Ryflex]

Hi all,

 

I tried Sunfighter's function's and I'm getting weird answers. I'll post code below and the actual times

 

 //resetten van de count en compare
  $count 	= 0;
  $compare	= -100;
   
  function differate($row_1, $row_2)
{
$answer = number_format((diffine($row_2) - diffine($row_1))/60);
echo $answer."<BR>";
if ($answer < 30) {
	return '1';
}	
if ($answer > 60) {
	return '0';
}
}

function diffine($time)
{
$mine = explode(':', $time);
$minetotal1 = $mine[0] * 3600 + $mine[1] * 60 + $mine[2];
return $minetotal1;
}
  
  //Gegevens ophalen uit de DB van de geselecteerde maand/jaar
  $dataqry	= mysql_query("SELECT tijd FROM `Standby` WHERE `maand` = '$maand' && `jaar` = '$jaar' ORDER BY jaar ASC, maand ASC, dag ASC, tijd ASC");
  if(!$dataqry) {
die("Storingen kunnen niet gevonden worden.");
  }

  while($row = mysql_fetch_assoc($dataqry)) {
  //$count ++;
    if(differate($compare, $row['tijd']) == '1') {
  $count++;
  
  $compare	= $row['tijd'];
}
  }
  echo $count."<BR>";
  //echo $compare."<BR>";

}

 

Times:

00:20:16

06:24:34

06:46:03

18:00:00

18:38:59

20:49:06

22:46:03

23:13:28

23:28:22

23:51:12

23:51:20

 

Edit: The value of count should be 8

[Ryflex]

Hi all,

 

With only the diffine function from sunfighter I managed to get the script working.

 

Thanks for your help

 

Ryflex