title: Calculate average, variance and standard deviation of two numbers in two different rows/columns with sql / PHP on specific dates
body: <p>I have a Database with the following structure:</p>
  
  <pre><code>rowid       ID                  startTimestamp   endTimestamp   subject
  1           00:50:c2:63:10:1a   ...1000          ...1090        entrance
  2           00:50:c2:63:10:1a   ...1100          ...1270        entrance
  3           00:50:c2:63:10:1a   ...1300          ...1310        door1
  4           00:50:c2:63:10:1a   ...1370          ...1400        entrance
  .
  .
  .
  </code></pre>
  
  <p>With this SQL-Query i can get the average differences between the endTime and the startTime between one row and the following row, sorted by subject and ID, with their min,max,variance and standard deviation:</p>
  
  <pre><code>SELECT ID,AVG(diff) AS average,
     AVG(diff*diff) - AVG(diff)*AVG(diff) AS variance,
     SQRT(AVG(diff*diff) - AVG(diff)*AVG(diff)) AS stdev,
     MIN(diff) AS minTime,
     MAX(diff) AS maxTime
  
  FROM
  (SELECT t1.id, t1.endTimestamp,
          min(t2.startTimeStamp) - t1.endTimestamp AS diff
  FROM table1 t1
  INNER JOIN table1 t2
  ON t2.ID = t1.ID AND t2.subject = t1.subject
  AND t2.startTimestamp &gt; t1.startTimestamp  -- consider only later startTimestamps
  WHERE t1.subject = 'entrance'
  GROUP BY t1.id, t1.endTimestamp) AS diffs
  GROUP BY ID
  </code></pre>
  
  <p>This works fine, if i have only a few rows on the same day with less time differences, you can see it in this sqlfiddle:</p>
  
  <p><a href="http://sqlfiddle.com/#!2/6de73/1" rel="nofollow">http://sqlfiddle.com/#!2/6de73/1</a></p>
  
  <p>But when i have additional data on just a different day, i get bad values:</p>
  
  <p><a href="http://sqlfiddle.com/#!2/920b6/1" rel="nofollow">http://sqlfiddle.com/#!2/920b6/1</a></p>
  
  <p>Therefore, I want to calculate the avg, min, max, variance, standard deviation for each single day. </p>
  
  <p>I know that there are the DATE-Functions of MySQL but i couldn't get it done...can somebody help me? Or do i have to write a piece of PHP code that could handle this?</p>