title: Mysql: How to aggregate data into days based on half hourly timestamps?
body: <p>The question sounds more complex than it probably is. I have a very simple table with php timestamps (just integers, not MySQL Date Fields) and float values. </p>
  
  <pre><code>id | timestamp | values |
  </code></pre>
  
  <p>The values are stored in half hourly intervals, so each timestamp increments by half an hour.</p>
  
  <p>I would like to aggregate a weeks worth of data into days in one query, so the response looks a something like this (1317596400 = OCT 3RD 2011)</p>
  
  <pre><code>0 | 1317596400 | 10.5
  1 | 1317682800 | 9.65
  2 | 1317769200 | 8.1
  3 | 1317855600 | 9.6
  4 | 1317942000 | 10.11
  5 | 1318028400 | 12.1
  6 | 1318114800 | 11.8
  </code></pre>
  
  <p>Where timestamp is 00:00 or 12:00, anything to identify the day. </p>
  
  <p>Any ideas?</p>
  
  <p>UPDATE: </p>
  
  <p>I made some progress...</p>
  
  <pre><code>SELECT FROM_UNIXTIME(timestamp) as stamp, sum(data) FROM energy 
  WHERE
  timestamp &gt;= 1317423600
  AND
  timestamp &lt;= 1320105600
  GROUP BY (  DATE( stamp ) ) 
  </code></pre>
  
  <p><strike>but this seems to be limiting the response to a month. I don't seem to be able to query from the middle of 1 month to the the middle of another month.</strike></p>
  
  <p>Group by "Date" not "Day", otherwise you end up summing the values of (for example) May 1st and April 1st into one row. </p>