CODEWITHSUNDEEP
mysqlsql
Jason
Jason

Reputation: 7656

How to extract the week from a date in MySQL using arbitrary weekday as the start of the week?

I have a data set (say, "x" dated by "xdate") for which I would like to get weekly averages on a week by week basis. I can do this with SQL (for MYSQL) like so:

SELECT 
    YEAR(xdate), 
    WEEKOFYEAR(xdate), avg(x) 
FROM 
    xtable
GROUP BY 
    YEAR(xdate),
    WEEKOFYEAR(xdate);

However, I would also like to group the weeks using an arbitrary weekday as the "start of week." Looking at the documentation it seems one can make the WEEK call using a parameter to indicate the start of the week as Sunday or Monday. I would like to make a call having the start of the week be any day of the week. So averages could be from Wednesday to Wednesday, and so on. Is there a way to achieve this?

Upvotes: 4

Views: 146

Answers (1)

Lajos Veres
Lajos Veres

Reputation: 13725

You can shift the function logic by one day if you add one day.

For example:

SELECT
  YEAR(xdate),
  WEEKOFYEAR(date_add(xdate, interval 1 day) ),
  avg(x)
FROM
  xtable
GROUP BY
  YEAR(xdate),
  WEEKOFYEAR(date_add(xdate, interval 1 day));

This way you can can shift the beginning of the week as many days as you want.

Upvotes: 3

Related Questions