title: Simple Averaging Algorithm is Slightly Off. Why? Active Record/PostgreSQL issue?
body: <p>In my Rails app, I have two custom Rake Tasks running every 30 minutes.  <strong>Task A</strong> scrapes hourly prices from the internet and saves them to a database as HourlyPrice.  <strong>Task B</strong> goes into the db, takes hourly prices from each day for the last seven days, and averages them to create a new DailyAveragePrice record in a separate DB Table. </p>
  
  <p>However, when running Task B, the last day's (of the seven) average price is incorrect.</p>
  
  <p>After fiddling with the hourly prices of that day in an Excel spreadsheet, I see that the average price Task B is generating is the result of taking only the <em>last three hours</em> and averaging them. </p>
  
  <p>Task B is mostly done with this single query:</p>
  
  <pre><code>averages = HourlyPrice.where('date &gt;= ?', 7.days.ago).average(:price, :group =&gt; "DATE_TRUNC('day', date - INTERVAL '1 hour')")
  </code></pre>
  
  <p>I can't figure out why this is happening?</p>
  
  <p><strong>Clues</strong></p>
  
  <ol>
  <li>HourlyPrice has two attributes (datetime,price).  Each HourlyPrice actually represents a price for the previous hour.  So, source data lists a 24:00:00 price for each day which PostgreSQL does not want to import as is into a datetime column.  Instead, it converts all 24:00:00 prices to 00:00:00 of the next day.  To make up for this, I've tried to subtract an hour interval, as you can see in the query.  Is this causing the problem?</li>
  <li>My ActiveRecord's time zone is currently set to 'Mountain Time (US &amp; Canada)'.  That is where the price exchange is located.  I have not adjusted my PostgreSQL DB's timezone, and I believe it defaults to UTC.  When running Task B, I noticed that it was 9:20PM UTC, leaving three hours left in the UTC day, which might explain the averaging of only three HourlyPrices of the last of the seven days.  I'll try running Task B again in the next hour to see if it will average only two hours.  Update to come...  Is this timezone conflict causing a problem, or is what I am doing insulated from timezones since I have my own date columns?</li>
  </ol>
  
  <p><strong>UPDATE - Problem identified, but how to fix?</strong>
  Clue #2 is correct.  It is a timezone issue.  I just ran Task B again (an hour later, with 2 hours left until UTC day change), and it only averages two HourlyPrices now for the last of the seven days.  </p>
  
  <p>How can I fix my query above to average ONLY if there are 24 HourlyPrice records available?  </p>