Databases
StackOverflow2013
Postsdbo
POHow to Efficiently use SQL to Retrieve Data on Half Hour Intervals?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to Efficiently use SQL to Retrieve Data on Half Hour Intervals?
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UShalfer
    1. USPontusTrade
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to Efficiently use SQL to Retrieve Data on Half Hour Intervals?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to Efficiently use SQL to Retrieve Data on Half Hour Intervals?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to Efficiently use SQL to Retrieve Data on Half Hour Intervals?
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODo you have any indexes ?
      singulars
      1. POHow to Efficiently use SQL to Retrieve Data on Half Hour Intervals?
      1. USSamson
    2. CO@radashk The sale_id is the primary index and links to another table called sales which has the products sold per receipt. Some receipts have 3 products sold, while others have only one so I seperated it into a one to many relational database.
      singulars
      1. POHow to Efficiently use SQL to Retrieve Data on Half Hour Intervals?
      1. USPontusTrade
    3. COIndex the time column. I don't see any considerable improvements else
      singulars
      1. POHow to Efficiently use SQL to Retrieve Data on Half Hour Intervals?
      1. USSamson
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload