title: How to optimizely fetch MySQL data from query for charts grouped by week
body: <p>My Mysql's table structure is like:</p>
  
  <p>Table name : <strong>visitor_sessions_data</strong></p>
  
  <pre>
  
  ------------------------------
  date_time           | visitor_sessions
  ------------------------------
  2016-12-01 14:00:00 | 2
  ------------------------------
  2016-12-01 18:50:10 | 1
  ------------------------------
  2016-12-19 06:00:00 | 1
  ------------------------------
  2016-12-20 16:00:00 | 1
  ------------------------------
  2017-02-02 19:00:00 | 3
  ------------------------------
  </pre>
  
  <p>and so on...</p>
  
  <p>And I want to fetch the data to display in graphs in daily, weekly and monthly order.</p>
  
  <p>I can easily fetch the data to render in chart if daily data is shown.
  For that I wrote: </p>
  
  <pre><code>SELECT date('date_time'), visitor_sessions 
  FROM visitor_sessions_data 
  WHERE (date('date_time')&gt;='2016-12-01') AND (date('date_time')&lt;='2017-02-10') 
  GROUP BY date('date_time')
  </code></pre>
  
  <p>So this gives me the data count grouped by each date.</p>
  
  <p>The issue I am facing in calculating the Weekly data as per my own thinking.
  I am actually unable to build optimized logic for fetching the weekly data from this table.</p>
  
  <p>My requirement is to provide data to chart in following manner:</p>
  
  <pre><code>Week 1: 3,
  Week 2: 2,
  Week 3: 0,
  Week 4: 0,
  Week 5: 0,
  ....
  Week X: 3,
  </code></pre>
  
  <p>Please note: Here week 1 means it should start counting the first date as week 1 (which I provide in mysql), and a week starts from <strong>Monday</strong></p>
  
  <p>I tried using this sql but it is only grouping and showing records for weeks where data exists, and doesn't show data for weeks where <strong>visitor_sessions</strong> are <strong>zero</strong>:</p>
  
  <pre><code>SELECT DATE_FORMAT(payment_added,'%X-%V'), visitor_sessions 
  FROM visitor_sessions_data 
  WHERE (date('date_time')&gt;='2016-12-01') AND (date('date_time')&lt;='2017-02-10') 
  GROUP BY DATE_FORMAT(payment_added,'%X-%V')
  </code></pre>
  
  <p>Please help.</p>
  
  <p>Thanks.</p>
  
  <p>I also seek your help in building query for getting grouped data for
  - monthly
  - quarterly
  - yearly</p>