MySQL group by week interval on Linux timestamps starting from Sunday

Question

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

Have you tried converting the timestamp to a UTC date, and then using the date functions? — fubar, Jan 06 '20 at 22:43
@fubar yes, but the date functions that group by week tend to end the week early at the end of the year, and dont always start on Sunday at the start of the new year. — Ice76, Jan 06 '20 at 22:44
Please provide sample data and expected results so you can get more accurate answers. — GMB, Jan 06 '20 at 22:48
@GMB Added what I am looking for, but the members joining dates go back years — Ice76, Jan 06 '20 at 23:00

O. Jones · Accepted Answer · 2020-01-06T23:26:02.543

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.

Thank you, I will test this. Either way, the links provide me with very good information, specifically about `YEARWEEK` mod that regulates complete weeks! — Ice76, Jan 06 '20 at 23:11
Im just getting syntax error to do with my MySQL version... sorting through it. — Ice76, Jan 06 '20 at 23:37
I had a couple of typographical errors in my code, but I edited my answer to fix them. Sorry about that, See the db-fiddle. — O. Jones, Jan 06 '20 at 23:39

MLKu · Answer 2 · 2020-01-09T01:01:07.423

my code comes from O. Jones, below are what i try:

mysql> 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_UNIXTI
ME(joined)) -1, 7)) as week_beginning
    ->   FROM members
    ->  GROUP BY FROM_DAYS(TO_DAYS(FROM_UNIXTIME(joined)) - MOD(TO_DAYS(FROM_UNI
XTIME(joined)) -1, 7))
    ->  ORDER BY new_members DESC
    -> ;
+-------------+-------------+--------------+----------------+
| new_members | last_joined | first_joined | week_beginning |
+-------------+-------------+--------------+----------------+
|           4 |  1578183400 |   1578182420 | 2020-01-05     |
|           3 |  1576082420 |   1576082400 | 2019-12-08     |
+-------------+-------------+--------------+----------------+
2 rows in set (0.07 sec)

2020-01-05 and 2020-01-05 are Sundays

below i replace 2nd to_days() with weekday():

mysql> 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(WEEKDAY(FROM_UNIXTIME(join
ed))+1,7)) as week_beginning
    -> FROM members
    -> GROUP BY FROM_DAYS(TO_DAYS(FROM_UNIXTIME(joined)) - MOD(WEEKDAY(FROM_UNIX
TIME(joined))+1,7))
    -> ORDER BY new_members DESC
    ->
    -> ;
+-------------+-------------+--------------+----------------+
| new_members | last_joined | first_joined | week_beginning |
+-------------+-------------+--------------+----------------+
|           4 |  1578183400 |   1578182420 | 2020-01-05     |
|           3 |  1576082420 |   1576082400 | 2019-12-08     |
+-------------+-------------+--------------+----------------+
2 rows in set (0.00 sec)

mysql>

and get the same results.

BTW,weekday() begin from monday:

Monday    -> 0
Tuesday   -> 1
Wednesday -> 2
Thursday  -> 3
Friday    -> 4
Saturday  -> 5
Sunday    -> 6

to_days() begin from Saturday, below prove it:

mysql> select to_days('2020-1-4');
+---------------------+
| to_days('2020-1-4') |
+---------------------+
|              737793 |
+---------------------+
1 row in set (0.00 sec)

mysql> select mod(to_days('2020-1-4'),7);
+----------------------------+
| mod(to_days('2020-1-4'),7) |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

mysql>

Thanks for your response, but this is a copy of the answer above... you even used the same `week_beginning` as he did (which I didn't name). — Ice76, Jan 07 '20 at 18:26

MySQL group by week interval on Linux timestamps starting from Sunday

2 Answers2