MySQL group by week interval on Linux timestamps starting from Sunday

I have a table of users that have joined and the column that tracked the timestamp of when they joined is a UNIX timestamp.

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.

The query for Week grouping:

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

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.

I have tried to FLOOR( joined / 604800 ) AS weekno but that is inaccurate since it starts from the earliest or latest record, and I need the week to start on Sunday, like:

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

Does anyone have any tips?

Sample data that I am looking for

member_id | joined
1         | 1578182420
2         | 1578182430
3         | 1578182500
4         | 1578183400
5         | 1576082400
6         | 1576082410
7         | 1576082420

Result:

new_members | last_joined | first_joined | week_start
4           | 1578183400  | 1578181400   | 1578182400
3           | 1576082420  | 1576082400   | 1577577600

Answer

Here’s what you want. This expression takes any unixtimestamp value and converts it to a DATETIME value that’s midnight on the Sunday of the week containing your unixtimestamp.

FROM_DAYS(TO_DAYS(FROM_UNIXTIME(unixtimestamp)) - 
      MOD(TO_DAYS(FROM_UNIXTIME(unixtimestamp)) -1, 7))

So this query should do the trick for you.

SELECT COUNT(member_id) as new_members,
       MAX(joined) as last_joined,
       MIN(joined) as first_joined,
       FROM_DAYS(TO_DAYS(FROM_UNIXTIME(joined)) - 
             MOD(TO_DAYS(FROM_UNIXTIME(joined)) -1, 7) week_beginning
  FROM members
 WHERE member_group_id NOT IN (2, 4, 7) 
 GROUP BY FROM_DAYS(TO_DAYS(FROM_UNIXTIME(joined)) - 
             MOD(TO_DAYS(FROM_UNIXTIME(joined)) -1, 7)
 ORDER BY new_members DESC

I like to use this stored function for the purpose. It’s easier to write and read your queries when you use it.

DELIMITER $$
DROP FUNCTION IF EXISTS TRUNC_SUNDAY$$
CREATE
  FUNCTION TRUNC_SUNDAY(datestamp DATETIME)
  RETURNS DATE DETERMINISTIC NO SQL
  COMMENT 'returns preceding Sunday'
  RETURN FROM_DAYS(TO_DAYS(datestamp) -MOD(TO_DAYS(datestamp) -1, 7))$$

If you use the stored function you can write your query like this (https://www.db-fiddle.com/f/cbtf9rueAvtFNUxE1PS387/0)

SELECT COUNT(member_id) as new_members,
       MAX(joined) as last_joined,
       MIN(joined) as first_joined,
       TRUNC_SUNDAY(FROM_UNIXTIME(joined)) week_beginning
  FROM members
 GROUP BY TRUNC_SUNDAY(FROM_UNIXTIME(joined))
 ORDER BY new_members DESC

If you want your weeks to start on Mondays, use -2 instead of -1 in the expression.

See this and this.

As a bonus, this technique honors your local timezone when figuring out the calendar week of any unixtimestamp.