title: Best way to count rows by arbitrary time intervals
body: <p>My app has a <code>Events</code> table with time-stamped events.</p>
  
  <p>I need to report the count of events during each of the most recent <code>N</code> time intervals. For different reports, the interval could be "each week" or "each day" or "each hour" or "each 15-minute interval".</p>
  
  <p>For example, a user can display how many orders they received each week, day, or hour, or quarter-hour.</p>
  
  <p>1) My preference is to dynamically do a single SQL query (I'm using Postgres) that groups by an arbitrary time interval. Is there a way to do that?</p>
  
  <p>2) An easy but ugly brute force way is to do a single query for all records within the start/end timeframe sorted by timestamp, then have a method manually build a tally by whatever interval.</p>
  
  <p>3) Another approach would be add separate fields to the event table for each interval and statically store an <code>the_week</code> <code>the_day</code>, <code>the_hour</code>, and <code>the_quarter_hour</code> field so I take the 'hit' at the time the record is created (once) instead of every time I report on that field.</p>
  
  <p>What's best practice here, given I could modify the model and pre-store interval data if required (although at the modest expense of doubling the table width)?</p>