Jump to content

Unique time difference query?

Drewser33

By Drewser33
in PHP Coding Help

Recommended Posts

Drewser33 Member

Drewser33

Posted
Posted

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

 

Link to comment
https://forums.phpfreaks.com/topic/94754-unique-time-difference-query/
Share on other sites
cooldude832 Advanced Member

cooldude832

Posted
Posted

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 Member

craygo

Posted
Posted

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 Member

Drewser33

Posted
  • Author
Posted

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 Member

craygo

Posted
Posted

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 Member

Drewser33

Posted
  • Author
Posted

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 Member

craygo

Posted
Posted

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 Member

Drewser33

Posted
  • Author
Posted

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 Member

craygo

Posted
Posted

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 Member

Drewser33

Posted
  • Author
Posted

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 Member

craygo

Posted
Posted

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 Member

Drewser33

Posted
  • Author
Posted

That is what I did when I started  ;D.  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

craygo Member

craygo

Posted
Posted

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

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.