title: How to Efficiently use SQL to Retrieve Data on Half Hour Intervals?
body: <p><strong>Problem - Retrieve sum of subtotals on a half hour interval efficiently</strong></p>
  
  <p>I am using MySQL and I have a table containing subtotals with different times. I want to retrieve the sum of these sales on a half hour interval from 7 am through 12 am. My current solution (below) works but takes 13 seconds to query about 150,000 records. I intend to have several million records in the future and my current method is too slow.</p>
  
  <p>How I can make this more efficient or if possible replace the PHP component with pure SQL? Also, would it help your solution to be even more efficient if I used Unix timestamps instead of having a date and time column?</p>
  
  <p><strong>Table Name - Receipts</strong></p>
  
  <pre><code>subtotal    date        time      sale_id
  --------------------------------------------
     6        09/10/2011  07:20:33     1
     5        09/10/2011  07:28:22     2
     3        09/10/2011  07:40:00     3
     5        09/10/2011  08:05:00     4
     8        09/10/2011  08:44:00     5
  ...............
    10        09/10/2011  18:40:00     6
     5        09/10/2011  23:05:00     7
  </code></pre>
  
  <p><strong>Desired Result</strong></p>
  
  <p>An array like this:</p>
  
  <ul>
  <li>Half hour 1 ::: (7:00 to 7:30) => Sum of Subtotal is 11</li>
  <li>Half hour 2 ::: (7:30 to 8:00) => Sum of Subtotal is 3</li>
  <li>Half hour 3 ::: (8:00 to 8:30) => Sum of Subtotal is 5</li>
  <li>Half hour 4 ::: (8:30 to 9:00) => Sum of Subtotal is 8</li>
  </ul>
  
  <p><strong>Current Method</strong></p>
  
  <p>The current way uses a for loop which starts at 7 am and increments 1800 seconds, equivalent to a half hour. As a result, this makes about 34 queries to the database.</p>
  
  <pre><code>for($n = strtotime("07:00:00"), $e = strtotime("23:59:59"); $n &lt;= $e; $n += 1800) {  
  
      $timeA = date("H:i:s", $n);
      $timeB = date("H:i:s", $n+1799);
  
      $query = $mySQL-&gt; query ("SELECT SUM(subtotal)
                                FROM Receipts WHERE time &gt; '$timeA' 
                                AND time &lt; '$timeB'");
  
      while ($row = $query-&gt; fetch_object()) {
          $sum[] = $row;
      }
  }
  </code></pre>
  
  <p><strong>Current Output</strong></p>
  
  <p>Output is just an array where:</p>
  
  <ul>
  <li>[0] represents 7 am to 7:30 am</li>
  <li>[1] represents 7:30 am to 8:00 am</li>
  <li><p>[33] represents 11:30 pm to 11:59:59 pm.</p>
  
  <p>array ("0" => 10000,
         "1" => 20000,
         ..............
         "33" => 5000);</p></li>
  </ul>