title: MySQL group by week interval on Linux timestamps starting from Sunday
body: <p>I have a table of users that have joined and the column that tracked the timestamp of when they joined is a UNIX timestamp. </p>
  
  <p>I want to group them by a weeks time in seconds, 604800, but am running into a roadblock. The other searches use MySQL week, but that is not what I am after since those weeks are not always full at the end of the year and vary depending on starting day.</p>
  
  <p>The query for Week grouping:</p>
  
  <pre><code>SELECT 
      COUNT(member_id) as new_members,
      MAX(joined) as last_joined,
      MIN(joined) as first_joined,
      YEAR(FROM_UNIXTIME(joined)) AS yr,
      MONTH(FROM_UNIXTIME(joined)) AS mn,
      WEEK(FROM_UNIXTIME(joined)) AS wk
  FROM members
  WHERE member_group_id NOT IN (2, 4, 7) 
  GROUP BY `yr`,`mn`,`wk`
  ORDER BY new_members DESC
  </code></pre>
  
  <p>I want to group my users by timestamp starting from next Sunday down. So, it would be the following Sunday, and one week intervals backwards until I run out of records.</p>
  
  <p>I have tried to <code>FLOOR( joined / 604800 ) AS weekno</code> but that is inaccurate since it starts from the earliest or latest record, and I need the week to start on Sunday, like:</p>
  
  <pre><code>SELECT COUNT(member_id) as new_members, 
         MAX(joined) as last_joined, MIN(joined) as first_joined, 
         FLOOR( joined / 604800 ) AS weekno 
  FROM `members` 
  WHERE member_group_id NOT IN (2, 4, 7) 
  GROUP BY `weekno` 
  ORDER BY weekno DESC
  </code></pre>
  
  <p>Does anyone have any tips?</p>
  
  <p>Sample data that I am looking for</p>
  
  <pre><code>member_id | joined
  1         | 1578182420
  2         | 1578182430
  3         | 1578182500
  4         | 1578183400
  5         | 1576082400
  6         | 1576082410
  7         | 1576082420
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>new_members | last_joined | first_joined | week_start
  4           | 1578183400  | 1578181400   | 1578182400
  3           | 1576082420  | 1576082400   | 1577577600
  </code></pre>