Unique time difference query?

[Drewser33]

All,

 

I have a query that will give me results like these.

 

Message = Turned ON Code = "OID" Strtotime = xxxxxxxxxx

Message = Turned OFF Code = "POP" Strtotime = yyyyyyyyyy

Message = Turned ON Code = "OID" Strtotime = aaaaaaaaaa

Message = Turned OFF Code = "POP" Strtotime = bbbbbbbbbb

Message = Turned ON Code = "OID" Strtotime = cccccccccc

Message = Turned OFF Code = "POP" Strtotime = dddddddd

Message = Turned ON Code = "OID" Strtotime = eeeeeeeee

Message = Turned ON Code = "OID" Strtotime = ffffffffff

Message = Turned ON Code = "OID" Strtotime = gggggggg

Message = Turned OFF Code = "POP" Strtotime = hhhhhhhh

 

The goal is to only display the only the first Code of "OID" if there are multiple as the last bit there demonstrates.

Then to have the time between every "POP" and "OID" like yyyyyyyyyy - xxxxxxxxxx for each and then the end one would be hhhhhhhh - eeeeeeeee??

 

Any ideas??

 

Thanks in advance

 

[cooldude832]

you can try something like

select MAX(Date)-MIN(Date) as total_time count(DATE) as row_count from `table` where Date >= DATE_MIN and Date <= DATE_MAX

Look up the exact sql on your own

 

The where statement could also be rewritten to be based on the primary key

i.e

Where EntryID >= 15 and Entry ID <= 20

 

I also added the count so you u can get teh avg time between

[craygo]

Here is something you can try Just change your fields in the query to match you db

<?php
// Set initial values
$lastcode = "";
$diff = 0;
$ptime=0;
$otime=0;
$sql = "SELECT string1 AS action, string2 AS code, UNIX_TIMESTAMP(timestamp) AS strtime FROM testing ORDER BY strtime ASC";
$res = mysql_query($sql) or die(mysql_error());
while($r = mysql_fetch_assoc($res)){
  if($r['code'] != $lastcode){
    if($r['code'] == "OID"){
    $otime = $r['strtime'];
    $ptime = $ptime;
    } else {
    $otime = $otime;
    $ptime = $r['strtime'];
    }
  echo "<p>Message = Turned ".$r['action']." Code = \"".$r['code']."\" Strtotime = ".$r['strtime']."</p>\n";
    if($r['code'] == "POP"){
    $diff = $ptime - $otime;
    echo "<p>$diff</p>\n";
    $ptime = 0;
    $otime = 0;
    }
  $ptime = $r['strtime'];
  $otime = $r['strtime'];
  }
$lastcode = $r['code'];
}
?>

 

output I get is

Message = Turned ON Code = "OID" Strtotime = 1204828288

 

Message = Turned OFF Code = "POP" Strtotime = 1204828320

 

Difference = 32

 

Message = Turned ON Code = "OID" Strtotime = 1204828337

 

Message = Turned OFF Code = "POP" Strtotime = 1204828376

 

Difference = 39

 

Message = Turned ON Code = "OID" Strtotime = 1204828406

 

Message = Turned OFF Code = "POP" Strtotime = 1204828445

 

Difference = 39

 

 

Ray

[Drewser33]

Thank you craygo as I am trying to implement your way of doing this.

 

I am obviously a very basice and new user of PHP and even SQL.  where you have string1 in your query I am having a tough time selecting everything.  I understand your idea as far as the rest of the code, just not the query.  In the table I am using, the field that the "CODE" resides is called EventCode and the I get the time stamp from a field called TimeOccured.  I don't know if this matters but there are a lot of other codes involved in the table but I am only worried about OID and POP.

 

Thanks for your help-

Drew

[craygo]

Well I use a table to test my code. the table has fields for all kinds of data types. so i use the string1 and string1 fields for this particular case.

 

in your query you would replace string1 with the field that has the "ON' and "OFF" in it . Change the string2 field to "EventCode" and change timestamp to "TimeOccured". If there alot of other codes you would have to add that to the query

$sql = "SELECT string1 AS action, EventCode AS code, UNIX_TIMESTAMP(TimeOccurred) AS strtime FROM testing WHERE Eventcode IN ('OID','POP') ORDER BY strtime ASC";

 

Ray

[Drewser33]

Awesome, thank you so much.  I am still not seeing where to check it as solved so if there is a way to do that please let me know.

 

Thanks again.

 

Drew

[Drewser33]

HI,

 

OK, everything is working well if there is only one ID to run.  The situation I am having trouble with now is if the data in the table is something like this:

 

Message = Turned ON Code = "OID" Strtotime = xxxxxxxxxx By ID = 1

Message = Turned ON Code = "OID" Strtotime = yyyyyyyyyy BY ID = 2

Message = Turned OFF Code = "POP" Strtotime = aaaaaaaaaa BY ID = 2

Message = Turned OFF Code = "POP" Strtotime = bbbbbbbbbb BY ID = 1

 

This is resulting in the math to subract aaaaaaaaa-xxxxxxxxx

 

I would like to select each possible ID, then run the query and math given below but making sure that it is using the correct ID's.

 

Here is the code I am trying to accomplish this with, but it only runs the query for the last DeviceSN in table.

Also, OID is basically APP and POP is APR in my example below.

 

$query1b = "SELECT DeviceSN From DeviceData";

$result1b = odbc_exec($odbc,$query1b) or die (odbc_error());

while($row1b = odbc_fetch_array($result1b));

 

{

// Set initial values

$ID = $row1b['DeviceSN'];

$lastcodeb = "";

$diffb = 0;

$ptimeb=0;

$otimeb=0;

$sqlb = "SELECT EventMessage AS action, EventCode AS code, TimeOccured AS strtime FROM EventLogger WHERE Eventcode IN ('APP','APR') AND ObjectID = $ID ORDER BY TimeOccured ASC";

$resb = odbc_exec($odbc,$sqlb) or die(odbc_error());

 

}

 

?>

<table>

<tr>

<td>

<b>Pull Cord Summary:</b>

</td>

</tr>

</table>

 

<?php

 

while($rb = odbc_fetch_array($resb)){

 

  if($rb['code'] != $lastcodeb){

    if($rb['code'] == "APP"){

    $otimeb = $rb['strtime'];

    $ptimeb = $ptimeb;

    } else {

    $otimeb = $otimeb;

    $ptimeb = $rb['strtime'];

    }

    $timeoccb = strtotime($rb['strtime']);

    $dateoccb = date('m-d-Y H:i:s' , $timeoccb);

 

  echo "<p>Message = ".$rb['action']." at ".$dateoccb."</p>\n";

 

  if($rb['code'] == "APR" AND $otimeb > 0){

    $diffb1[] = strtotime($ptimeb) - strtotime($otimeb);

   

    $diffb = strtotime($ptimeb) - strtotime($otimeb);

    $diffb = datediff($otimeb,$ptimeb);

    echo "<p>$diffb</p>\n";

   

    $ptimeb = 0;

    $otimeb = 0;

    }

  $ptimeb = $rb['strtime'];

  $otimeb = $rb['strtime'];

  }

$lastcodeb = $rb['code'];

}

 

$timetotalb = array_sum($diffb1) ;

$numb = sizeof($diffb1);

$avgb = $timetotalb / $numb;

$avgb1= round($avgb/60,2);

$avgb1a= ceil($avgb);

$avgb2 = timecreator($avgb1a);

 

 

?>

<table>

<tr>

<td>

<?php echo 'Total Time in Alarm ' . timecreator($timetotalb); ?>

</td>

<td>

<?php echo 'Average Alarm Time ' . $avgb2 ; ?>

</td>

</tr>

</table>

<table width="300" align = "center">

<tr>

<td><div align="center"><hr width="100%"></div></td>

</tr>

</table>

 

 

Hope this makes sense, and thanks in advance.

[craygo]

Try adding in another order by with the field that has the ID in it. I am not sure what the field is called so I will call it "ObjectID"

 

Also if you want more than one id you will have to take out the filter for the objectID

 

$sqlb = "SELECT EventMessage AS action, EventCode AS code, TimeOccured AS strtime FROM EventLogger WHERE Eventcode IN ('APP','APR') ORDER BY ObjectID ASC, TimeOccured ASC";

 

Ray

[Drewser33]

I can't believe that it was a simple order by.  Thanks for your help.

 

Drew

[Drewser33]

Alright after doing some more careful checking this is an issue that is occurring:

 

Assuming all same ID:

 

Message = Turned ON Code = "OID" Strtotime = eeeeeeeee

Message = Turned ON Code = "OID" Strtotime = ffffffffff

Message = Turned ON Code = "OID" Strtotime = gggggggg

Message = Turned OFF Code = "POP" Strtotime = hhhhhhhh

 

Is returning gggggggg-hhhhhhhhh

I would like it to do:  eeeeeeeeeee - hhhhhhhhh

 

Any ideas?

 

Code is still as on last posts.

[craygo]

Really cause I have set things up in the database to simulate what you have and it is working correctly.

 

Here is my data exported as a csv file

"id";"string1";"string2";"string3";"date1";"date2";"datetime1";"timestamp";"number1";"utime";"blob1";"month";"day";"year";"number2"
"1";"ON";"OID";;NULL;"0000-00-00";"0000-00-00 00:00:00";"2008-03-06 13:31:28";NULL;NULL;;;;;"0"
"2";"OFF";"POP";;NULL;"0000-00-00";"0000-00-00 00:00:00";"2008-03-06 13:32:00";NULL;NULL;;;;;"0"
"3";"ON";"OID";;NULL;"0000-00-00";"0000-00-00 00:00:00";"2008-03-06 13:32:17";NULL;NULL;;;;;"0"
"4";"OFF";"POP";;NULL;"0000-00-00";"0000-00-00 00:00:00";"2008-03-06 13:32:56";NULL;NULL;;;;;"0"
"5";"ON";"OID";;NULL;"0000-00-00";"0000-00-00 00:00:00";"2008-03-06 13:33:26";NULL;NULL;;;;;"0"
"6";"ON";"OID";;NULL;"0000-00-00";"0000-00-00 00:00:00";"2008-03-06 13:33:38";NULL;NULL;;;;;"0"
"7";"ON";"OID";;NULL;"0000-00-00";"0000-00-00 00:00:00";"2008-03-06 13:33:50";NULL;NULL;;;;;"0"
"8";"OFF";"POP";;NULL;"0000-00-00";"0000-00-00 00:00:00";"2008-03-06 13:34:05";NULL;NULL;;;;;"0"

 

Like I said before disregard all the extra columns I use the table for testing

 

As you can see I have 3 rows with OID

 

my results are

Message = Turned ON Code = "OID" Strtotime = 2008-03-06 13:31:28

Message = Turned OFF Code = "POP" Strtotime = 2008-03-06 13:32:00

32

Message = Turned ON Code = "OID" Strtotime = 2008-03-06 13:32:17

Message = Turned OFF Code = "POP" Strtotime = 2008-03-06 13:32:56

39

Message = Turned ON Code = "OID" Strtotime = 2008-03-06 13:33:26

Message = Turned OFF Code = "POP" Strtotime = 2008-03-06 13:34:05

39

 

So you can see it is grabbing the first OID and calculating with that

 

Ray

 

[Drewser33]

Ok, so I think I found the issue, but I am back to in a lot of trouble if this doesn't work.  I started using mysql as that is what I am familiar with.  Unfortunately I have been told to build this using MSAccess.  I am sure my trouble is when I converted everything from mysql to odbc. 

 

Here is how i changed it and see if you can see an issue as I don't see how it is different, but I am not that good at this:

 

Code for ODBC:

 

$query1b = "SELECT DeviceSN From DeviceData";

$result1b = odbc_exec($odbc,$query1b) or die (odbc_error());

while($row1b = odbc_fetch_array($result1b))

 

{

// Set initial values

$lastcodeb = "";

$diffb = 0;

$ptimeb=0;

$otimeb=0;

$sqlb = "SELECT EventMessage AS action, EventCode AS code, TimeOccured AS strtime FROM EventLogger WHERE Eventcode IN ('APP','APR') ORDER BY ObjectID ASC, TimeOccured ASC";

$resb = odbc_exec($odbc,$sqlb) or die(odbc_error());

 

 

}

 

 

 

 

 

?>

<table>

<tr>

<td>

<b>Pull Cord Summary:</b>

</td>

</tr>

</table>

 

<?php

 

while($rb = odbc_fetch_array($resb)){

 

  if($rb['code'] != $lastcodeb){

    if($rb['code'] == "APP"){

    $otimeb = $rb['strtime'];

    $ptimeb = $ptimeb;

    } else {

    $otimeb = $otimeb;

    $ptimeb = $rb['strtime'];

    }

    $timeoccb = strtotime($rb['strtime']);

    $dateoccb = date('m-d-Y H:i:s' , $timeoccb);

 

  echo "<p>Message = ".$rb['action']." at ".$dateoccb."</p>\n";

 

  if($rb['code'] == "APR" AND $ptimeb > $otimeb){

    $diffb1[] = strtotime($ptimeb) - strtotime($otimeb);

   

    $diffb = strtotime($ptimeb) - strtotime($otimeb);

    $diffb = datediff($otimeb,$ptimeb);

    echo "<p>$diffb</p>\n";

   

    $ptimeb = 0;

    $otimeb = 0;

    }

  $ptimeb = $rb['strtime'];

  $otimeb = $rb['strtime'];

  }

$lastcodeb = $rb['code'];

}

 

$timetotalb = array_sum($diffb1) ;

$numb = sizeof($diffb1);

$avgb = $timetotalb / $numb;

$avgb1= round($avgb/60,2);

$avgb1a= ceil($avgb);

$avgb2 = timecreator($avgb1a);

 

 

?>

<table>

<tr>

<td>

<?php echo 'Total Time in Alarm ' . timecreator($timetotalb); ?>

</td>

<td>

<?php echo 'Average Alarm Time ' . $avgb1 ; ?>

</td>

</tr>

</table>

<table width="300" align = "center">

<tr>

<td><div align="center"><hr width="100%"></div></td>

</tr>

</table>

 

and because doesn't work, I used this function so that it would be more like mysql that I understand better:

 

if(!function_exists('odbc_fetch_array'))

{

  function odbc_fetch_array($result, $rownumber=-1)

  {

      if (PHP_VERSION > '4.1')

      {

          if ($rownumber < 0)

          {

              odbc_fetch_into($result, $rs);

          }

          else

          {

              odbc_fetch_into($result, $rs, $rownumber);

          }

      }

      else

      {

          odbc_fetch_into($result, $rownumber, $rs);

      }

     

      $rs_assoc = Array();

 

      foreach ($rs as $key => $value)

      {

          $rs_assoc[odbc_field_name($result, $key+1)] = $value;

      }

      return $rs_assoc;

  }

}

 

[craygo]

MSaccess my god! how bought you fake them out and create the msaccess file and link the data to the mysql tables that way your queries can still come from mysql.

 

not all sql statements may work try taking the sql statement into access and go to query and hit the design button to go to sql view and paste in the sql statement and see if it works.

 

RAy

[Drewser33]

That is what I did when I started  .  But when if I can get this working it will be working on a lot of databases (400) or more and they update every second so to convert them might get to be a bit of overhead.

 

I am pretty sure the query is working, but I will take your advice and try it in Access.

 

I think its somewhere in that function its not looking at the array like it does in mysql??

 

Drew

[Drewser33]

Ok, I did get the correct information in the Access query, it built the results with all OID's and POP's.  So the query is working.

 

I don't know, any ideas or am I "f'd" because they "have" to use Access?

[craygo]

no you shouldn't be. just might be the way the odbc works. I haven't used it in a while so let me try and recreate it here and see what happens.

 

What version of access you using??

 

Ray

[Drewser33]

Access 2003. 

 

Thanks for all your help I really do appreciate it.

 

Drew

[craygo]

OK this worked for me, Seems like access doesn't like a few things that mysql does

 

Here is the code i used, again change the field names to fit your table.

<?php
// Set initial values
$lastcode = "";
$diff = 0;
$ptime=0;
$otime=0;
$sql = "SELECT `string1` AS action, `string2` AS code, `timestamp` FROM testing ORDER BY timestamp ASC";
$res = odbc_exec($conn, $sql) or die(odbc_error($conn));
while($r = odbc_fetch_array($res)){
$unixtime = date("U", strtotime($r['timestamp']));
  if($r['code'] != $lastcode){
    if($r['code'] == "OID"){
    $otime = $unixtime;
    $ptime = $ptime;
    } else {
    $otime = $otime;
    $ptime = $unixtime;
    }
  echo "<p>Message = Turned ".$r['action']." Code = \"".$r['code']."\" Strtotime = ".date("Y-m-d G:i:s", $unixtime)."</p>\n";
    if($r['code'] == "POP"){
    $diff = $ptime - $otime;
    echo "<p>$diff</p>\n";
    $ptime = 0;
    $otime = 0;
    }
  $ptime = $unixtime;
  $otime = $unixtime;
  }
$lastcode = $r['code'];
}
?>

 

Ray

[Drewser33]

Looks, good.  You are great, thanks again!!