title: Grouping by each hour for each weekday
body: <p>I have a table <code>orders</code></p>
  
  <pre><code>  id  |  price  |  items_count  |         created_at
  ------+---------+---------------+------------------------
     1  |  16.50  |      3        | 2015-07-21 12:52:00.824
     2  |  50.00  |      1        | 2015-07-21 12:54:42.658
     3  |  12.00  |     10        | 2015-07-22 07:21:47.808
     .  |   .     |      .        |             .
     .  |   .     |      .        |             .
     .  |   .     |      .        |             .
  </code></pre>
  
  <p>and so on...</p>
  
  <p>Now I want to get <em>number of orders</em> and <em>sum of their prices</em> from last month grouped by <em>weekdays</em> (as numbers from 0 to 6) and <em>hours</em> (as numbers from 0 to 23).</p>
  
  <p>What I managed to do is:</p>
  
  <pre><code>SELECT EXTRACT('dow' FROM to_timezone(created_at, 'CEST', 0)) AS DAY,
  (EXTRACT('hour' FROM to_timezone(created_at, 'CEST', 0)) + 0)::INT % 24 AS HOUR,
  COUNT(DISTINCT id) AS orders_count,
  SUM(price) AS price_total
  FROM orders
  WHERE created_at BETWEEN '2015-06-30 22:00:00.000000'
  AND '2015-07-31 21:59:59.999999'
  GROUP BY extract('dow' from to_timezone(created_at, 'CEST', 0)),
  EXTRACT('hour' from to_timezone(created_at, 'CEST', 0))
  ORDER BY 1 ASC, EXTRACT('hour' from to_timezone(created_at, 'CEST', 0));
  </code></pre>
  
  <p>And here is what I get -  <strong>current result</strong>:</p>
  
  <pre><code> day | hour | orders_count | price_total
  -----+------+--------------+-------------
     0 |    7 |           11 |    298.00
     0 |    9 |            8 |     64.00
     1 |    8 |            1 |     12.50
     1 |   12 |            3 |     69.00 
     2 |   10 |            2 |    112.00
     2 |   13 |            1 |    100.00
     2 |   14 |           13 |   2163.70
     2 |   21 |            4 |    357.00
  </code></pre>
  
  <p>and so on...</p>
  
  <p>Now I want to include <strong>all hours</strong> in a day and put <code>0</code> in <code>orders_count</code> and <code>price_total</code> columns if there were no orders in given hour. So my <strong>desired result</strong> should look like this:</p>
  
  <pre><code> day | hour | orders_count | price_total
  -----+------+--------------+-------------
     0 |    0 |            0 |      0.00
     0 |    1 |            0 |      0.00
     0 |    2 |            0 |      0.00
     0 |    3 |            0 |      0.00
     0 |    4 |            0 |      0.00
     0 |    5 |            0 |      0.00
     0 |    6 |            0 |      0.00
     0 |    7 |           11 |    298.00
     0 |    8 |            0 |      0.00
     0 |    9 |            8 |     64.00
     0 |   10 |            0 |      0.00
  </code></pre>
  
  <p>and so on - I need 7 days <code>[0,6]</code> and for each day 24 hours <code>[0,23]</code> with <code>0</code> in aggregate columns.</p>
  
  <hr>
  
  <p>I came up with an idea to use <code>generate_series</code> to get <strong>all hours</strong> in a day:</p>
  
  <pre><code>SELECT EXTRACT(hour from generate_series)
  FROM generate_series('2015-07-01 00:00'::timestamp, '2015-07-01 23:00', '1 hour');
  </code></pre>
  
  <p>My problem is that I don't know how to combine my <strong>current result</strong> with <strong>all hours</strong>. What kind of <code>JOIN</code> should I use? And how to insert <code>0</code>s in previously mentioned columns when there are no orders for a given row?</p>