title: Number of specific one-hour periods between two date/times
body: <p>I have a table of table records, call it "game"</p>
  
  <p>It has an id and timestamp.  </p>
  
  <p>What I need to know is unrelated to the table specifically.  In order to know the average number of games played per hour, I need to know :</p>
  
  <ul>
  <li>Total games played for each hour over the date range </li>
  <li>Number of hourly
  periods between the date range.</li>
  </ul>
  
  <p>Finding the first is a matter of extracting the hour from the timestamp and grouping by it.
  For the second, if the date range was rounded to the nearest day, finding this value would be easy (totalgames/numdays).  </p>
  
  <p>Unfortunately I can't assume this.  What I need help with is finding the number of <strong>specific</strong> hour periods existing within a time range.</p>
  
  <p>Example:
  If the range is 5 PM today to 8 PM tomorrow, there is one "00" hour (midnight to 1 AM), but <strong>two</strong> 17, 18, 19 hours (5-6, 6-7, 7-8)</p>
  
  <p>Thanks for the help</p>
  
  <p>Edit: for clarity, consider the following query:</p>
  
  <p>I have table game:</p>
  
  <p>id, daytime</p>
  
  <pre><code>select EXTRACT(hour from daytime) as hour_period, count (*)
  from game
  where daytime &gt; dateFrom and daytime &lt; dayTo
  group by hour_period
  </code></pre>
  
  <p>This will give me the number of games played broken down into hourly chunks for the time period.</p>
  
  <p>In order to find the average games played per hour, I need to know exactly how many <strong>specific</strong> hour durations are between two timestamps.  Simply dividing by the number of days is not accurate.</p>
  
  <p>Edit:  The ideal output will look something like this:</p>
  
  <pre><code>00   275
  01   300
  02   255
  ...
  </code></pre>
  
  <p>Consider the following:  How many times does midnight occur between date 1 and date 2 ?  If you have 1.5 days, that doesn't guarantee that midnight will occur twice.  6 AM today to 6 PM tomorrow night, for example, has 1 midnight, but 9PM tonight to 9 AM two days from now has 2 midnights.  </p>
  
  <p>What I'm trying to find is how many of the EXACT HOUR occurs between two timestamps, so I can use it to average the number of games played at THAT HOUR over a time period.</p>