title: How to query hourly aggregated data by date with postgresql?
body: <p>There is one table:</p>
  
  <pre><code>ID DATE 
  1  2017-09-16 20:12:48
  2  2017-09-16 20:38:54
  3  2017-09-16 23:58:01
  4  2017-09-17 00:24:48
  5  2017-09-17 00:26:42
  ..
  </code></pre>
  
  <p>The result I need is the last 7-days of data with hourly aggregated count of rows:</p>
  
  <pre><code>COUNT DATE
  2     2017-09-16 21:00:00
  0     2017-09-16 22:00:00
  0     2017-09-16 23:00:00
  1     2017-09-17 00:00:00
  2     2017-09-17 01:00:00
  ..
  </code></pre>
  
  <p>I tried different stuff with EXTRACT, DISTINCT and also used the generate_series function (most stuff from similar stackoverflow questions) </p>
  
  <p>This try was the best one currently:</p>
  
  <pre><code>SELECT 
    date_trunc('hour', demotime) as date,
    COUNT(demotime) as count
  FROM demo
  GROUP BY date
  </code></pre>
  
  <p>How to generate hourly series for 7 days and fill-in the count of rows?</p>