title: SQL get temperature value by minute average
body: <p>I have a micro-controller with a Ethernet interface and 3 temperature sensors connected to them.
  The temperature data is sent to a different machine that is running a script that put's the values into a MySQL database by sensor id.</p>
  
  <p>The temperature values are sent by the micro-controller 45-50 times a minute.</p>
  
  <p>I am trying to get a minute wise average to use with a graphing tool, but I'm having problems figuring out how to get the average pr minute of the past 24 hours.</p>
  
  <p>the data looks like this:</p>
  
  <pre><code>+----------+-----------------+---------------------+
  | sensorID | sensorTempValue | sensorTimeStamp     |
  +----------+-----------------+---------------------+
  |        3 |             320 | 2013-12-09 09:38:00 |
  |        2 |             156 | 2013-12-09 09:38:01 |
  |        1 |             360 | 2013-12-09 09:38:02 |
  |        3 |             320 | 2013-12-09 09:38:04 |
  |        2 |             156 | 2013-12-09 09:38:05 |
  |        1 |             360 | 2013-12-09 09:38:06 |
  |        3 |             319 | 2013-12-09 09:38:08 |
  |        2 |             156 | 2013-12-09 09:38:09 |
  |        1 |             360 | 2013-12-09 09:38:10 |
  |        3 |             319 | 2013-12-09 09:38:12 |
  |        2 |             156 | 2013-12-09 09:38:13 |
  </code></pre>
  
  <p>......</p>
  
  <p>The sensorID is the ID for each of the DS18x20 digital sensors and sensorTempValue is the raw data it returns so divide that value by 16 and you get the Celsius temperature value.</p>
  
  <pre><code>SELECT sensorTimeStamp, sensorTempValue/16 
  FROM (SELECT * 
        FROM temperatureData 
        WHERE sensorTimeStamp &gt; DATE_SUB(now(), INTERVAL 1 DAY) 
        AND sensorID = 2 
        GROUP BY MINUTE(sensorTimeStamp)) 
  m order by sensorTimeStamp
  </code></pre>
  
  <p>This is the closest I've gotten, but this is still not for the whole 23 hours.
  I'm guessing this could be done with grouping among other things.</p>