title: Efficient time series querying in Postgres
body: <p>I have a table in my PG db that looks somewhat like this:</p>
  
  <pre><code>id | widget_id | for_date | score |
  </code></pre>
  
  <p>Each referenced widget has a lot of these items. It's always 1 per day per widget, but there are gaps.</p>
  
  <p>What I want to get is a result that contains all the widgets for each date since X. The dates are brought in via generate series:</p>
  
  <pre><code> SELECT date.date::date
     FROM generate_series('2012-01-01'::timestamp with time zone,'now'::text::date::timestamp with time zone, '1 day') date(date)
   ORDER BY date.date DESC;
  </code></pre>
  
  <p>If there is no entry for a date for a given widget_id, I want to use the previous one. So say widget 1337 doesn't have an entry on 2012-05-10, but on 2012-05-08, then I want the resultset to show the 2012-05-08 entry on 2012-05-10 as well:</p>
  
  <pre><code>Actual data:
  widget_id | for_date   | score
  1312      | 2012-05-07 | 20
  1337      | 2012-05-07 | 12
  1337      | 2012-05-08 | 41
  1337      | 2012-05-11 | 500
  
  Desired output based on generate series:
  widget_id | for_date   | score
  1336      | 2012-05-07 | 20
  1337      | 2012-05-07 | 12
  1336      | 2012-05-08 | 20
  1337      | 2012-05-08 | 41
  1336      | 2012-05-09 | 20
  1337      | 2012-05-09 | 41
  1336      | 2012-05-10 | 20
  1337      | 2012-05-10 | 41
  1336      | 2012-05-11 | 20
  1337      | 2012-05-11 | 500
  </code></pre>
  
  <p>Eventually I want to boil this down into a view so I have consistent data sets per day that I can query easily.</p>
  
  <p><strong>Edit:</strong> Made the sample data and expected resultset clearer</p>