title: Group By Week on Redshift
body: <p>I'm trying to count my table by week but the <code>DATE_TRUNCT('week',date)</code> function considers Monday as the start of the week but I need for the week to start in Sunday.</p>
  
  <p>This is the query, which runs properly but with starting in Mondays...</p>
  
  <pre><code>SELECT DATE_TRUNC('week',myDate) AS Reference,
         column1 AS Item1,
         column2 AS Item2,
         COUNT(*) AS Volume,
         COUNT(CASE WHEN status = 'status1' THEN 1 END) AS Status1,
         COUNT(CASE WHEN status = 'status2' THEN 1 END) AS Status2,
         COUNT(CASE WHEN status = 'status2' AND fase = '1' THEN 1 END) AS Fase1,
         COUNT(CASE WHEN status = 'status2' AND fase = '2' THEN 1 END) AS Fase2,
         COUNT(CASE WHEN status = 'status2' AND fase = '3' THEN 1 END) AS Fase3
  FROM myTable
  WHERE DATE_TRUNC('week',myDate) = DATE_TRUNC('week',TO_DATE('12/25/2016 00:00:00','MM/dd/yyyy'))
  GROUP BY 1,
           2,
           3;
  </code></pre>
  
  <p>So far I only tried another query which doesnt even run and I dont know why, it just says "syntax error at or near "integer" :</p>
  
  <pre><code>SELECT DATE_TRUNC('week',myDate) - integer '1' AS Reference,
         column1 AS Item1,
         column2 AS Item2,
         COUNT(*) AS Item3,
         COUNT(CASE WHEN status = 'status1' THEN 1 END) AS Status1,
         COUNT(CASE WHEN status = 'status2' THEN 1 END) AS Status2,
         COUNT(CASE WHEN status = 'status2' AND fase = '1' THEN 1 END) AS Fase1,
         COUNT(CASE WHEN status = 'status2' AND fase = '2' THEN 1 END) AS Fase2,
         COUNT(CASE WHEN status = 'status2' AND fase = '3' THEN 1 END) AS Fase3
  FROM myTable
  WHERE myDate between ( DATE_TRUNC('week', TO_DATE('12/25/2016 00:00:00','MM/dd/yyyy' ) - integer '1' ) and ( DATE_TRUNC('week', TO_DATE('12/25/2016 00:00:00','MM/dd/yyyy' ) ) + integer '5' )
  GROUP BY 1,
           2,
           3;
  </code></pre>
  
  <p>Also, even if this query runned propely, it would show the count of the week 18/Dec - 24/Dec and not the week 25/Dec - 31/Dec in the case 25/Dec. The same would happen in other days if they are Sundays.</p>
  
  <p>EDIT:
  I just found the solution in this blog:
  <a href="https://blog.modeanalytics.com/date-trunc-sql-timestamp-function-count-on/" rel="nofollow noreferrer">https://blog.modeanalytics.com/date-trunc-sql-timestamp-function-count-on/</a>
   It was introducing the date_trunct function and someone asked the same question in the comments. This is my solved query for future reference to others:</p>
  
  <pre><code>SELECT date_trunc('WEEK',(myDate + interval '1 day'))- interval '1 day' AS Reference
         column1 AS Item1,
         column2 AS Item2,
         COUNT(*) AS Volume,
         COUNT(CASE WHEN status = 'status1' THEN 1 END) AS Status1,
         COUNT(CASE WHEN status = 'status2' THEN 1 END) AS Status2,
         COUNT(CASE WHEN status = 'status2' AND fase = '1' THEN 1 END) AS Fase1,
         COUNT(CASE WHEN status = 'status2' AND fase = '2' THEN 1 END) AS Fase2,
         COUNT(CASE WHEN status = 'status2' AND fase = '3' THEN 1 END) AS Fase3
  FROM myTable
  WHERE  ( date_trunc('WEEK',(myDate + interval '1 day'))- interval '1 day') = ( DATE_TRUNC('week',TO_DATE('12/24/2016 00:00:00','MM/dd/yyyy') + interval '1 day'  ) - interval '1 day' )
  GROUP BY 1,
           2,
           3;
  </code></pre>