title: Postgres generate_series and crosstab in query
body: <p>I need a time series data but incorporate <code>generate_series</code> for times where there's no datapoint on the table. Also, a <code>crosstab</code> to pivot the records.</p>
  
  <p>I have the <code>generate_series</code> for the time frame needed:</p>
  
  <pre><code>SELECT generate_series('2015-01-01 00:00'::timestamp,'2015-03-31 23:00'::timestamp,interval '1 hour')::timestamp
  </code></pre>
  
  <p><strong>but</strong> i need help adding <code>generate_series</code> and <code>crosstab</code> to the query below:</p>
  
  <pre><code>SELECT   
      timestamp as timestamp_utc,
      id as auth_id,
      coalesce(value, 0) as market_price
  
  FROM table1 t1
  
  WHERE timestamp between '2015-01-01 00:00:00' and '2015-03-31 23:55:00'
      AND id in (42,54,38,51)
  </code></pre>
  
  <p><strong>Problem:</strong> some datapoints in <code>timestamp_utc</code> are missing and i would like to identify those using <code>generate_series</code>. Lastly <code>crosstab</code> the values of <code>AND t3.id in (42,54,38,51)</code> in a column pivot.</p>
  
  <p>Sample outcome should look like:</p>
  
  <pre><code>timestamp_utc      | 42    |54    |38    |51    |
  -------------------|-------|------|------|------|
  2015-01-01 01:00:00| 252.45| 0    | 1231 | 141.2|
  2015-01-01 02:00:00| 0     | 112.3| 0    | 245.1|
  2015-01-01 03:00:00| 0     | 234.1| 0    | 143.6|
  </code></pre>
  
  <p>Thanks for any help</p>