CODEWITHSUNDEEP
mysqlmysql-8.0week-number
Matt
Matt

Reputation: 3706

MySQL week number with specific first day of the week

With the MySQL WEEK function I can set the first day of the week to Sunday or Monday. But how about if I need to set it to some other day?

The reason for this is that we have clients going through online programs that have a specific "check-in day" each week. For some it will be Monday, for some Tuesday, for some Wednesday and so on, and we need to see the averages of multiple check-ins for each week.

If it was always Sunday and Monday I can do something like this:

SELECT YEAR(timestamp) AS year, WEEK(timestamp,1) AS week ... GROUP BY year, week

But there's thousands of clients and I need to get the weekly averages for each client, but each client will have their own first day of the week — going Monday to Sunday, Wednesday to Tuesday, Saturday to Friday and so on.

Any ideas how we can do this?

Thanks for your help!

Upvotes: 0

Views: 1023

Answers (1)

Scratte
Scratte

Reputation: 3166

I'm calling your timestamp time, to not confuse it with a datatype:

CREATE TABLE clientdates(client VARCHAR(20),
                         time   TIMESTAMP)

I suggest you just subtract the DAYOFWEEK("check-in day"), which you in comments refer to as entry_due_at from the time. Then you'll get the offset you need for the WEEK() to work:

SET @client := 'client2';

-- in my example I just picked the first date the client had an entry
SELECT @weekday := DAYOFWEEK(min(time)) - 1 -- need 0 to 6
  FROM clientdates
 WHERE client = @client;

SELECT client,
       @weekday,
       YEAR(time) AS year,
       WEEK(time - INTERVAL @weekday DAY) AS week_offset,
       count(*)
  FROM clientdates
 WHERE client = @client
 GROUP BY client, year, week_offset

Running it with this data, which will have it's first day on a Thursday:

INSERT INTO clientdates
  VALUES ('client2', '2021-08-19 04:14:07.9'),
         ('client2', '2021-08-20 04:14:07.9'),
         ('client2', '2021-08-21 04:14:07.9'),
         ('client2', '2021-08-22 04:14:07.9'),
         ('client2', '2021-08-23 04:14:07.9'),
         ('client2', '2021-08-24 04:14:07.9'),
         ('client2', '2021-08-25 04:14:07.9'),
         ('client2', '2021-08-26 04:14:07.9'),
         ('client2', '2021-08-27 04:14:07.9'),
         ('client2', '2021-08-28 04:14:07.9'),
         ('client2', '2021-08-29 04:14:07.9');

I get this result:

client @weekday year week_offset count(*)
client2 2 2021 33 7
client2 2 2021 34 4

If you need to use WEEK(time, 1) instead, you'll just need to subtract 2 instead of 1 when getting the value for @weekday, since you need Monday to have the zero-indexed value instead of Sunday.

dbfiddle

Upvotes: 1

Related Questions