MySQL - count of consecutive hours

[Mucello]

I have a table with columns like this:
ob (datetime)
temperature (float)

There should be one record for each hour in a day (ob). I need to query this table and return a count of the number of consecutive records preceeding each hour within an 8 hour timeframe where the temperature > 70 degrees.

I know how to get the number of records spread out during a timeframe, but I'm having trouble finding consecutive records.

[void_function]

I am not sure how to do it using SQL alone, but I'd tell you how I would do it with PHP.

First, query the database and obtain all rows for the wanted timeframe (don't filter for temperature). If one record was inserted each hour, then those should be consequtive regarding time, or order by time.

$res= mysql_query("your query here");

Then,

$iTemperature1=0;
$iCount=0;
for ($i=0; $i<mysql_num_rows($res); $i++) {
  $obj= mysql_fetch_object($res);
  $iTemperature2= $obj->temperature_column;
  if ($i) {
    if (($iTemperature1 == $iTemperature2) && $iTemperature2>70)
      $iCount++;
  }
  $iTemperature1=$obj->temperature_column;
}

Remember not to put a where clause filtering for temp>70, because you'll end up with all consecutive rows. 🙂

Hope this helps.