title: Efficient way to sum measurements / time series by given interval in php
body: <p>I have a series of measurement data / time series in the same interval of 15 minutes. Furthermore, I have a given period (e.g. one day, current week, month, year, (...) and I need to summarize values by hour, day, month, (...). </p>
  
  <p>E.g. summarize all values of the last month, by day. </p>
  
  <p>My approach is to generate a temporary array with the needed interval per period in the first step. E.g. here in PHP (PHP is not that necessary, I would prefer Python or Javascript if it provides a faster method)</p>
  
  <pre><code>$this-&gt;tempArray = array(
  '2014-10-01T00:00:00+0100' =&gt; array(),
  '2014-10-02T00:00:00+0100' =&gt; array(),
  '2014-10-03T00:00:00+0100' =&gt; array(),
  '2014-10-04T00:00:00+0100' =&gt; array(),
  (...)
  '2014-10-31T00:00:00+0100' =&gt; array()
  );
  </code></pre>
  
  <p>In the second step, I loop through each date/value pair (in this example 4*24*31, (96 per day)) and assign them to my temporary array. For each date, I override some values from the datetime object. In this example the hour and the minutes to match the keys in the temp array.</p>
  
  <pre><code>$insert = array(
      'datetime' =&gt; $datetime,
      'value' =&gt; $value
  );
  
  if ($interval == "d") {
  
      $this-&gt;tempArray[date('Y-m-d\T00:00:sO', $datetime)][] = $insert;
  }
  </code></pre>
  
  <p>At the last step, I loop through the temp array and summarize each array. As the result, I receive an array with 31 new date/values pairs, summarized by each day. This works fine. However is there a faster way or more efficient way? It takes nearly 0.5 seconds with this approach for one month. (If someone is interested in the source code, I will add a gist). The data are stored within a mysql database with 15 mio entries. </p>
  
  <p>// Edit: I think the best way is to group this with mysql.</p>
  
  <p>My current SQL query to fetch data from one year:</p>
  
  <pre><code>SELECT
  FROM_UNIXTIME(PointOfTime)) as `date`,
  value
  FROM data
  WHERE EnergyMeterId="0ca64479-bddf-4b91-9e35-bf81f4bfa84c"
  and PointOfTime &gt;= unix_timestamp('2013-01-01T00:00:00')
  and PointOfTime &lt;= unix_timestamp('2013-12-31T23:45:00')
  order by `date` asc;
  </code></pre>