title: Postgres group by timestamp into 6 hourly buckets
body: <p>I have the following simple table:</p>
  
  <pre><code>ID      TIMESTAMP               VALUE
  4   2011-05-27 15:50:04 1253
  5   2011-05-27 15:55:02 1304
  6   2011-05-27 16:00:02 1322
  7   2011-05-27 16:05:01 1364
  </code></pre>
  
  <p>I would like to average the <code>VALUES</code>, and <code>GROUP</code> each <code>TIMESTAMP</code> day into 6 hourly buckets. e.g 00:00 to 06:00, 06:00 to 12:00, 12:00 to 18:00 &amp; 18:00 to 00:00.</p>
  
  <p>I am able to group by year, month, day &amp; hour using the following query:</p>
  
  <pre><code>select avg(VALUE),
    EXTRACT(year from TIMESTAMP) AS year,
    EXTRACT(month from TIMESTAMP) AS month,
    EXTRACT(day from TIMESTAMP) as day
      from TABLE
        group by year,month,day
  </code></pre>
  
  <p>But I am unable to group each day into 4 periods as defined above, any help is most welcome.</p>